package com.cqupt.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.cqupt.service.BusinessService;
import com.cqupt.service.MonitorService;
import com.cqupt.service.ProblemService;
import com.cqupt.service.ReportExportService;
import com.cqupt.utils.APIUtils;
import com.cqupt.utils.DateUtils;
import com.cqupt.utils.PageUtils;
import com.cqupt.utils.TextUtils;
import com.sun.org.apache.xpath.internal.operations.Bool;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;
import sun.misc.FpUtils;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import javax.xml.crypto.Data;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class ReportExportServiceImpl implements ReportExportService {

    @Autowired
    private ProblemService problemService;

    @Autowired
    private BusinessService businessService;

    /**
     *
     * 各个监控项数据导出
     * @param status
     * @param hostType
     */
    @Override
    public void ExportData(String status,Integer hostType) {
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        final Integer count = monitorService.getHostCountByTypeAndStatus(hostType,status);      //通过状态获得设别数量
        List<Map<String,Object>> resultList = monitorService.getHostListAvailClassify(1,count,status,hostType);     //筛选可用设备
        // 创建excel表格
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = wb.createSheet();
        // 创建一行
        HSSFRow row = sheet.createRow(0);
        // 单元格风格设定
        HSSFCellStyle style = wb.createCellStyle();
        // 居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 创建当前行单元格，第一列
        HSSFCell cell = row.createCell(0);
        // 设定单元格宽度
        cell.setCellValue("设备名称");
        sheet.setColumnWidth(0,6000);
        //设置单元格样式
        cell.setCellStyle(style);
        // 同上创建当前单元格，第二列
        cell = row.createCell(1);
        cell.setCellValue("CPU利用率(%)");
        sheet.setColumnWidth(1,6000);
        //设置单元格样式
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("内存利用率(%)");
        sheet.setColumnWidth(2,6000);
        //设置单元格样式
        cell.setCellStyle(style);
        cell = row.createCell( 3);
        cell.setCellValue("磁盘利用率(%)");
        sheet.setColumnWidth(3,6000);
        //设置单元格样式
        cell.setCellStyle(style);
        cell = row.createCell( 4);
        cell.setCellValue("磁盘IO速率(写/读)");
        sheet.setColumnWidth(4,6000);
        // 设定单元个风格
        cell.setCellStyle(style);

        for (int i=0;i<resultList.size();i++){
            //同上，创建新的一行
            row = sheet.createRow(i+1);
            //设置单元格样式
            row.createCell(0).setCellValue((String) resultList.get(i).get("hostname"));
            Map<String,Object> map = (Map<String, Object>) resultList.get(i).get("item");
            row.createCell(1).setCellValue((String)map.get("CPUUtilization"));
            row.createCell(2).setCellValue((String) map.get("MemoryUtilization"));
            row.createCell(3).setCellValue((String) map.get("DiskUtilization"));
            //设定Windows磁盘的读写速率
            if (map.get("DiskWriterateWindows").toString()!="" && map.get("DiskReadrateWindows").toString()!="")
                row.createCell(4).setCellValue(map.get("DiskWriterateWindows")+"/"+map.get("DiskReadrateWindows"));
            //设定Linux的读写速率
            else
                row.createCell(4).setCellValue(map.get("DiskWriterate")+"/"+map.get("DiskReadrate"));
        }
        try {
            // 采用字节流方式保存文件
            FileOutputStream fos = new FileOutputStream(new File("f:\\性能分析报表.xls"));
            // 写入
            wb.write(fos);
            // 关闭字节流
            fos.close();
        }catch (Exception e){
            e.getMessage();
        }
    }

    /**
     * 获得服务器监控项的信息
     * @param hostids
     * @param startTime
     * @param endTime
     * @param response
     * @return
     */
    @Override
    public Boolean getItem_by_DataInfo(String hostids,String startTime,String endTime,HttpServletResponse response) {
        int flag = 1;
        String status = "";
        Integer hostType = 0;
        // 格式化时间，ms级别的时间戳转为年月日时分秒
        long start = (DateUtils.timeToStamp(startTime))/1000;
        long end = (DateUtils.timeToStamp(endTime))/1000;
        List<Map<String,Object>> hostList = getAllOnlineHost(hostids);
        //判断类型
        if ("1".equals(hostList.get(0).get("available")))
            hostType = 0;
        if ("1".equals(hostList.get(0).get("snmp_available")))
            hostType = 1;
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        //获得监控项信息
        List<Map<String,Object>> resultMap = monitorService.getItemDataInfo(hostids,status,hostType);
        //通过设备id获得监控项
        flag = monitorService.getItemsByHostID(hostids);
        int i = 0;
        String itemid;
        if (hostType == 0) {
            //Linux
            if (flag == 1) {
                // 原理同上
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("CPU利用率");
                // 创建一行
                HSSFRow row = sheet.createRow(0);
                // 设置宽度
                sheet.setColumnWidth(0, 6000);
                // 设置宽度
                sheet.setColumnWidth(1, 6000);
                // 设置宽度
                sheet.setColumnWidth(2, 6000);
                // 设置宽度
                sheet.setColumnWidth(3, 6000);
                // 设置宽度
                sheet.setColumnWidth(4, 6000);
                // 设置宽度
                sheet.setColumnWidth(5, 6000);
                // 设置宽度
                sheet.setColumnWidth(6, 6000);
                HSSFCellStyle style = wb.createCellStyle();
                //设置中心对齐
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);


                HSSFCellStyle style_temp = wb.createCellStyle();
                //设置字体大小
                style_temp.setFillForegroundColor((short) 13);
                //设置中心对齐
                style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                // 字体加粗
                style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                HSSFCell cell = row.createCell(i);
                // 第0行
                Calendar c = Calendar.getInstance();
                //格式化时间格式
                c.add(Calendar.DAY_OF_MONTH, -1);
                // 格式化时间
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");

//                int todayhours =  ((new Date()).getHours()-1);
                //获得当前前一个小时
                int todayhours =  (c.get(Calendar.HOUR_OF_DAY)-1);
//                int yesterdayhours =  ((new Date()).getHours());
                //获得当前小时
                int yesterdayhours =  (c.get(Calendar.HOUR_OF_DAY));
                // 组装时间格式
                String today = df.format(new Date())+"  "+todayhours+":00";
                String yesterday = df.format(c.getTime())+"  "+yesterdayhours+":00";

                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                // 单元格设置风格
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                //第1行
                row = sheet.createRow(i);
                //合并单元格 第一列到第七列
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                //CPU利用率
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("CPU利用率(%)");
                cell.setCellStyle(style_temp);


                //第2行
                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                i++;
                //共i行
                //通过设备hostid何键值获得监控项
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");
                // 通过监控项获得趋势
                JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                // 没有数据
                if (array.isEmpty())
                    System.out.println("我是空，我是新的");
                //根据clock排序
                array.sort(Comparator.comparing(obj -> {
                    Integer value = ((JSONObject) obj).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj = (JSONObject) array.get(0);
                Long num = Long.valueOf(arr_obj.get("clock").toString());
                for (Iterator iterator = array.iterator(); iterator.hasNext(); ) {
                    JSONObject obj = (JSONObject) iterator.next();
                    if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)) {
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        //格式化时间
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }
                i = 0;
                sheet = wb.createSheet("内存利用率");
                //设置单元格大宽度
                sheet.setColumnWidth(0, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(1, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(2, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(3, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(4, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(5, 6000);
                //设置单元格大宽度
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                cell.setCellValue(startTime+"---"+endTime);
                //内存利用率
                row = sheet.createRow(i);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                //创建单元
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("内存利用率(%)");
                //设置单元格样式
                cell.setCellStyle(style_temp);



                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));


                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                i++;
                //通过设备hostid何键值获得监控项
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.utilization").get("result")).getJSONObject(0).getString("itemid");
                // 通过监控项获得趋势
                JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                //根据clock排序
                array1.sort(Comparator.comparing(obj1 -> {
                    Integer value = ((JSONObject) obj1).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj1 = (JSONObject) array1.get(0);
                //类型转换
                Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
                for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                    JSONObject obj1 = (JSONObject) iterator1.next();
                    if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);
                        // 单元格设值
                        cell = row.createCell(1);
                        cell.setCellValue(obj1.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        // 单元格设值
                        cell = row.createCell(3);
                        cell.setCellValue(obj1.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        // 单元格设值
                        cell = row.createCell(5);
                        cell.setCellValue(obj1.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }


                i = 0;

                sheet = wb.createSheet("磁盘利用率");
                //设置宽度
                sheet.setColumnWidth(0, 6000);
                //设置宽度
                sheet.setColumnWidth(1, 6000);
                //设置宽度
                sheet.setColumnWidth(2, 6000);
                //设置宽度
                sheet.setColumnWidth(3, 6000);
                //设置宽度
                sheet.setColumnWidth(4, 6000);
                //设置宽度
                sheet.setColumnWidth(5, 6000);
                //设置宽度
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                cell.setCellValue(startTime+"---"+endTime);
                //磁盘利用率
                row = sheet.createRow(i);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                cell = row.createCell(0);
                cell.setCellValue("磁盘利用率(%)");
                cell.setCellStyle(style_temp);


                row = sheet.createRow(i);
                // 单元格设值
                cell = row.createCell(0);
                cell.setCellValue("时间");
                cell.setCellStyle(style_temp);
                // 单元格设值
                cell = row.createCell(1);
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

                // 单元格设值
                cell = row.createCell(3);
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                // 单元格设值
                cell = row.createCell(5);
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                i++;
                //通过设备hostid何键值获得监控项
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[/,used]").get("result")).getJSONObject(0).getString("itemid");
                //通过设备hostid何键值获得监控项
                String itemid3 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[/,total]").get("result")).getJSONObject(0).getString("itemid");
                // 通过监控项获得趋势
                JSONArray array2 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                // 通过监控项获得趋势
                JSONArray array3 = (JSONArray) monitorService.getTrendByItemid(itemid3).get("result");
                // 通过clock排序
                array2.sort(Comparator.comparing(obj2 -> {
                    Integer value = ((JSONObject) obj2).getIntValue("clock");
                    return value;
                }).reversed());
                // 通过clock排序
                array3.sort(Comparator.comparing(obj3 -> {
                    Integer value = ((JSONObject) obj3).getIntValue("clock");
                    return value;
                }).reversed());

                JSONObject arr_obj2 = (JSONObject) array2.get(0);
                Long num2 = Long.valueOf(arr_obj2.get("clock").toString());
                Iterator iterator3 = array3.iterator();
                for (Iterator iterator2 = array2.iterator(); iterator2.hasNext(); ) {
                    JSONObject obj2 = (JSONObject) iterator2.next();
                    JSONObject obj3 = (JSONObject) iterator3.next();
                    if ((Long.valueOf(obj2.get("clock").toString()) >= start) &&(Long.valueOf(obj2.get("clock").toString()) <= end)) {
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj2.get("clock").toString()));
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 比率--->使用/全部 * 100%
                        cell.setCellValue(100*(double)Long.parseLong(obj2.get("value_avg").toString())/Long.parseLong(obj3.get("value_avg").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 比率--->使用/全部 * 100%
                        cell.setCellValue(100*(double)Long.parseLong(obj2.get("value_max").toString())/Long.parseLong(obj3.get("value_max").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(5);
                        // 比率--->使用/全部 * 100%
                        cell.setCellValue(100*(double)Long.parseLong(obj2.get("value_min").toString())/Long.parseLong(obj3.get("value_min").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }


                i = 0;
                sheet = wb.createSheet("磁盘IO速率");
                //合并单元格
                sheet.setColumnWidth(0, 6000);
                //合并单元格
                sheet.setColumnWidth(1, 6000);
                //合并单元格
                sheet.setColumnWidth(2, 6000);
                //合并单元格
                sheet.setColumnWidth(3, 6000);
                //合并单元格
                sheet.setColumnWidth(4, 6000);
                //合并单元格
                sheet.setColumnWidth(5, 6000);
                //合并单元格
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                cell.setCellValue(startTime+"---"+endTime);
                //磁盘利用率
                row = sheet.createRow(i);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("磁盘I/O速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);



                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                i++;

                row = sheet.createRow(i);
                cell = row.createCell(0);
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("读速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(3);
                cell.setCellValue("读速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(4);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                cell.setCellStyle(style_temp);

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("读速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(6);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                cell.setCellStyle(style_temp);
                i++;

                //通过设备hostid何键值获得监控项
                String itemid5 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.dev.write[,,]").get("result")).getJSONObject(0).getString("itemid");
                //通过设备hostid何键值获得监控项
                String itemid6 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.dev.read[,,]").get("result")).getJSONObject(0).getString("itemid");
                // 通过监控项获得趋势
                JSONArray array5 = (JSONArray) monitorService.getTrendByItemid(itemid5).get("result");
                // 通过监控项获得趋势
                JSONArray array6 = (JSONArray) monitorService.getTrendByItemid(itemid6).get("result");
                array5.sort(Comparator.comparing(obj5 -> {
                    Integer value = ((JSONObject) obj5).getIntValue("clock");
                    // 通过clock排序
                    return value;
                }).reversed());
                array6.sort(Comparator.comparing(obj6 -> {
                    Integer value = ((JSONObject) obj6).getIntValue("clock");
                    // 通过clock排序
                    return value;
                }).reversed());

                JSONObject arr_obj5 = (JSONObject) array5.get(0);
                Long num5 = Long.valueOf(arr_obj5.get("clock").toString());
                Iterator iterator6 = array6.iterator();
                for (Iterator iterator5 = array5.iterator(); iterator5.hasNext(); ) {
                    JSONObject obj5 = (JSONObject) iterator5.next();
                    JSONObject obj6 = (JSONObject) iterator6.next();
                    if ((Long.valueOf(obj5.get("clock").toString()) >= start) &&(Long.valueOf(obj5.get("clock").toString()) <= end)) {
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj5.get("clock").toString()));
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        cell = row.createCell(2);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        cell = row.createCell(4);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);


                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_min").toString());
                        cell.setCellStyle(style);
                        cell = row.createCell(6);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                    } else
                        continue;
                }


                //网卡流量
                // 获得设备网络设据
                List<Map<String,Object>> resultList = monitorService.getNetData(hostids);
                for (int j = 0; j < resultList.size(); j++) {
                    itemid = resultList.get(j).get("itemid").toString();
                    // 通过监控项获得趋势
                    JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                    array7.sort(Comparator.comparing(obj7 -> {
                        // 通过clock排序
                        Integer value = ((JSONObject) obj7).getIntValue("clock");
                        return value;
                    }).reversed());
                    JSONObject arr_obj7 = (JSONObject) array7.get(0);
                    Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                    if ("0".equals(arr_obj7.get("value_avg").toString()))
                        continue;
                    i = 0;
                    String str = resultList.get(j).get("name").toString();
                    String name = str.split(":")[0].substring(10);
                    String type = str.split(":")[1].split(" ")[2];
                    // 判断是发送流量/接受流量
                    if ("sent".equals(type))
                        name = name+" 发送流量";
                    else
                        name = name+" 接受流量";
                    sheet = wb.createSheet(name);
                    //设置单元格宽度
                    sheet.setColumnWidth(0, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(1, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(2, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(3, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(4, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(5, 6000);
                    //设置单元格宽度
                    sheet.setColumnWidth(6, 6000);
                    row = sheet.createRow(i);
                    cell = row.createCell(0);
                    i++;
                    // 单元格设值
                    cell.setCellValue("设备名称");
                    cell.setCellStyle(style_temp);
                    cell = row.createCell(1);
                    cell.setCellValue(resultMap.get(0).get("host").toString());
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("时间：");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    cell = row.createCell(3);
                    cell.setCellValue(startTime+"---"+endTime);
                    row = sheet.createRow(i);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList.get(j).get("name")+"  网络接口流量(kbps)");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    row = sheet.createRow(i);

                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue("时间");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue("平均");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("最大");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                    cell = row.createCell(5);
                    // 单元格设值
                    cell.setCellValue("最小");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                    i++;


                    for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                        JSONObject obj7 = (JSONObject) iterator7.next();
                        if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                            //合并单元格
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                            //合并单元格
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                            //合并单元格
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                            row = sheet.createRow(i);
                            i++;
                            cell = row.createCell(0);
                            // 单元格设值
                            cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                            //设置单元格样式
                            cell.setCellStyle(style);

                            cell = row.createCell(1);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_avg").toString());
                            cell.setCellStyle(style);

                            cell = row.createCell(3);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_max").toString());
                            //设置单元格样式
                            cell.setCellStyle(style);

                            cell = row.createCell(5);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_min").toString());
                            //设置单元格样式
                            cell.setCellStyle(style);
                        } else
                            continue;
                    }

                }


//                response.reset();
//                response.addHeader("Access-Control-Allow-Origin", "*");
//                response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
                // 设定保存类型 excel
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                // 保存文件的名称
                String fileName = "性能分析报表";
                try {
                    // 保存文件编码格式
                    String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
                    // 保存文件头部默认数据
                    response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                    // 字节流保存
                    OutputStream os = response.getOutputStream();
                    // 写入
                    wb.write(os);
                    // 刷新缓冲区
                    os.flush();
                    // 关闭字节流
                    os.close();
                }catch (Exception e){
                    e.getMessage();
                }


            }
            //Windows
            else if (flag == 0) {
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("CPU利用率");
                HSSFRow row = sheet.createRow(0);
                sheet.setColumnWidth(0, 6000);

                sheet.setColumnWidth(1, 6000);
                sheet.setColumnWidth(2, 6000);

                sheet.setColumnWidth(3, 6000);
                sheet.setColumnWidth(4, 6000);

                sheet.setColumnWidth(5, 6000);
                sheet.setColumnWidth(6, 6000);
                HSSFCellStyle style = wb.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);


                HSSFCellStyle style_temp = wb.createCellStyle();
                style_temp.setFillForegroundColor((short) 13);
                style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                HSSFCell cell = row.createCell(i);
                // 第0行
                Calendar c = Calendar.getInstance();
                c.add(Calendar.DAY_OF_MONTH, -1);
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//                int todayhours =  ((new Date()).getHours()-1);
                int todayhours =  (c.get(Calendar.HOUR_OF_DAY)-1);
//                int yesterdayhours =  ((new Date()).getHours());
                int yesterdayhours =  (c.get(Calendar.HOUR_OF_DAY));
                String today = df.format(new Date())+"  "+todayhours+":00";
                String yesterday = df.format(c.getTime())+"  "+yesterdayhours+":00";


                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                //第1行
                row = sheet.createRow(i);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                //CPU利用率
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("CPU利用率(%)");
                //设置单元格样式
                cell.setCellStyle(style_temp);


                //第2行
                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                i++;
                //共i行
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                array.sort(Comparator.comparing(obj -> {
                    Integer value = ((JSONObject) obj).getIntValue("clock");// 通过clock排序
                    return value;
                }).reversed());
                JSONObject arr_obj = (JSONObject) array.get(0);
                Long num = Long.valueOf(arr_obj.get("clock").toString());
                for (Iterator iterator = array.iterator(); iterator.hasNext(); ) {
                    JSONObject obj = (JSONObject) iterator.next();
                    if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)) {
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(obj.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }
                i = 0;
                sheet = wb.createSheet("内存利用率");
                sheet.setColumnWidth(0, 6000);

                sheet.setColumnWidth(1, 6000);
                sheet.setColumnWidth(2, 6000);

                sheet.setColumnWidth(3, 6000);
                sheet.setColumnWidth(4, 6000);

                sheet.setColumnWidth(5, 6000);
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                //内存利用率
                row = sheet.createRow(i);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("内存利用率(%)");
                //设置单元格样式
                cell.setCellStyle(style_temp);



                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));


                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));

                i++;
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                array1.sort(Comparator.comparing(obj1 -> {
                    Integer value = ((JSONObject) obj1).getIntValue("clock");// 通过clock排序
                    return value;
                }).reversed());
                JSONObject arr_obj1 = (JSONObject) array1.get(0);
                Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
                for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                    JSONObject obj1 = (JSONObject) iterator1.next();
                    if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj1.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj1.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(obj1.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }
                i = 0;
                sheet = wb.createSheet("磁盘利用率");
                sheet.setColumnWidth(0, 6000);

                sheet.setColumnWidth(1, 6000);
                sheet.setColumnWidth(2, 6000);

                sheet.setColumnWidth(3, 6000);
                sheet.setColumnWidth(4, 6000);

                sheet.setColumnWidth(5, 6000);
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                //磁盘利用率
                row = sheet.createRow(i);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("磁盘利用率(%)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                i++;

                //初始化变量
                String itemidC1="",itemidC2="",itemidD1="",itemidD2="",itemidE1="",itemidE2="",itemidF1="",itemidF2="",itemidG1="",itemidG2="";
                JSONArray arrayC1=null,arrayC2=null,arrayD1=null,arrayD2=null,arrayE1=null,arrayE2=null,arrayF1=null,arrayF2=null,arrayG1=null,arrayG2=null;
                JSONArray arrC1=null,arrC2=null,arrD1=null,arrD2=null,arrE1=null,arrE2=null,arrF1=null,arrF2=null,arrG1=null,arrG2=null;
                Long numFinal = 0l;
                int count = 0;

                // C盘
                arrayC1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[C:,used]").get("result"));//通过设备hostid何键值获得监控项
                if (!arrayC1.isEmpty()){
                    count++;
                    arrayC2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[C:,total]").get("result"));//通过设备hostid何键值获得监控项
                    itemidC1 = arrayC1.getJSONObject(0).getString("itemid");
                    itemidC2 = arrayC2.getJSONObject(0).getString("itemid");
                    arrC1 = (JSONArray) monitorService.getTrendByItemid(itemidC1).get("result");
                    arrC1.sort(Comparator.comparing(objC1 -> {
                        Integer value = ((JSONObject) objC1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrC2 = (JSONArray) monitorService.getTrendByItemid(itemidC2).get("result");
                    arrC2.sort(Comparator.comparing(objC2 -> {
                        Integer value = ((JSONObject) objC2).getIntValue("clock");
                        return value;
                    }).reversed());
                    numFinal = Long.valueOf(((JSONObject) arrC1.get(0)).get("clock").toString());
                }

                // D盘
                arrayD1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[D:,used]").get("result"));
                if (!arrayD1.isEmpty()){
                    count++;
                    arrayD2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[D:,total]").get("result"));
                    itemidD1 = arrayD1.getJSONObject(0).getString("itemid");
                    itemidD2 = arrayD2.getJSONObject(0).getString("itemid");
                    arrD1 = (JSONArray) monitorService.getTrendByItemid(itemidD1).get("result");
                    arrD1.sort(Comparator.comparing(objD1 -> {
                        Integer value = ((JSONObject) objD1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrD2 = (JSONArray) monitorService.getTrendByItemid(itemidD2).get("result");
                    arrD2.sort(Comparator.comparing(objD2 -> {
                        Integer value = ((JSONObject) objD2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                // E盘
                arrayE1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[E:,used]").get("result"));
                if (!arrayE1.isEmpty()){
                    count++;
                    arrayE2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[E:,total]").get("result"));
                    itemidE1 = arrayE1.getJSONObject(0).getString("itemid");
                    itemidE2 = arrayE2.getJSONObject(0).getString("itemid");
                    arrE1 = (JSONArray) monitorService.getTrendByItemid(itemidE1).get("result");
                    arrE1.sort(Comparator.comparing(objE1 -> {
                        Integer value = ((JSONObject) objE1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrE2 = (JSONArray) monitorService.getTrendByItemid(itemidE2).get("result");
                    arrE2.sort(Comparator.comparing(objE2 -> {
                        Integer value = ((JSONObject) objE2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                // F盘
                arrayF1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[F:,used]").get("result"));
                if (!arrayF1.isEmpty()){
                    count++;
                    arrayF2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[F:,total]").get("result"));
                    itemidF1 = arrayF1.getJSONObject(0).getString("itemid");
                    itemidF2 = arrayF2.getJSONObject(0).getString("itemid");
                    arrF1 = (JSONArray) monitorService.getTrendByItemid(itemidF1).get("result");
                    arrF1.sort(Comparator.comparing(objF1 -> {
                        Integer value = ((JSONObject) objF1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrF2 = (JSONArray) monitorService.getTrendByItemid(itemidF2).get("result");
                    arrF2.sort(Comparator.comparing(objF2 -> {
                        Integer value = ((JSONObject) objF2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                //G盘
                arrayG1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[G:,used]").get("result"));
                if (!arrayG1.isEmpty()){
                    count++;
                    //计算g盘磁盘容量
                    arrayG2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[G:,total]").get("result"));
                    itemidG1 = arrayG1.getJSONObject(0).getString("itemid");
                    itemidG2 = arrayG2.getJSONObject(0).getString("itemid");
                    arrG1 = (JSONArray) monitorService.getTrendByItemid(itemidG1).get("result");
                    //根据clock排序
                    arrG1.sort(Comparator.comparing(objG1 -> {
                        Integer value = ((JSONObject) objG1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrG2 = (JSONArray) monitorService.getTrendByItemid(itemidG2).get("result");
                    arrG2.sort(Comparator.comparing(objG2 -> {
                        Integer value = ((JSONObject) objG2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                // 盘不为空时获得数据
                Iterator iteratorC2 = arrC2.iterator();

                Iterator iteratorD1 = arrC1.iterator();
                Iterator iteratorD2 = arrC2.iterator();
                if (!arrayD1.isEmpty()){
                    iteratorD1 = arrD1.iterator();
                    iteratorD2 = arrD2.iterator();
                }

                Iterator iteratorE1 = arrC1.iterator();
                Iterator iteratorE2 = arrC2.iterator();
                if (!arrayE1.isEmpty()){
                    iteratorE1 = arrE1.iterator();
                    iteratorE2 = arrE2.iterator();
                }

                Iterator iteratorF1 = arrC1.iterator();
                Iterator iteratorF2 = arrC2.iterator();
                if (!arrayF1.isEmpty()){
                    iteratorF1 = arrF1.iterator();
                    iteratorF2 = arrF2.iterator();
                }

                Iterator iteratorG1 = arrC1.iterator();
                Iterator iteratorG2 = arrC2.iterator();
                if (!arrayG1.isEmpty()){
                    iteratorG1 = arrG1.iterator();
                    iteratorG2 = arrG2.iterator();
                }

                int temp = 0;
                // 计算最大/最小/平均值
                for (Iterator iteratorC1 = arrC1.iterator(); iteratorC1.hasNext(); ) {
                    Long avg_use = 0l , max_use = 0l , min_use = 0l;
                    Long avg_total = 0l , max_total = 0l , min_total = 0l;
                    JSONObject objC1 = (JSONObject) iteratorC1.next();
                    JSONObject objC2 = (JSONObject) iteratorC2.next();

                    JSONObject objD1 = null;
                    JSONObject objD2 = null;
                    if (!arrayD1.isEmpty()) {
                        objD1 = (JSONObject) iteratorD1.next();
                        objD2 = (JSONObject) iteratorD2.next();
                    }

                    JSONObject objE1 = null;
                    JSONObject objE2 = null;
                    if (!arrayE1.isEmpty()) {
                        objE1 = (JSONObject) iteratorE1.next();
                        objE2 = (JSONObject) iteratorE2.next();
                    }

                    JSONObject objF1 = null;
                    JSONObject objF2 = null;
                    if (!arrayF1.isEmpty()) {
                        objF1 = (JSONObject) iteratorF1.next();
                        objF2 = (JSONObject) iteratorF2.next();
                    }

                    JSONObject objG1 = null;
                    JSONObject objG2 = null;
                    if (!arrayG1.isEmpty()) {
                        objG1 = (JSONObject) iteratorG1.next();
                        objG2 = (JSONObject) iteratorG2.next();
                    }

                    if ((Long.valueOf(objC1.get("clock").toString()) >= start) &&(Long.valueOf(objC1.get("clock").toString()) <= end)) {
                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                        sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(objC1.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);


                        // 求和
                        if (!arrayC1.isEmpty()){
                            avg_use += Long.valueOf(objC1.get("value_avg").toString());
                            max_use += Long.valueOf(objC1.get("value_min").toString());
                            min_use += Long.valueOf(objC1.get("value_max").toString());
                            avg_total += Long.valueOf(objC2.get("value_avg").toString());
                            max_total += Long.valueOf(objC2.get("value_min").toString());
                            min_total += Long.valueOf(objC2.get("value_max").toString());
                        }

                        if (!arrayD1.isEmpty()){
                            avg_use += Long.valueOf(objD1.get("value_avg").toString());
                            max_use += Long.valueOf(objD1.get("value_min").toString());
                            min_use += Long.valueOf(objD1.get("value_max").toString());
                            avg_total += Long.valueOf(objD2.get("value_avg").toString());
                            max_total += Long.valueOf(objD2.get("value_min").toString());
                            min_total += Long.valueOf(objD2.get("value_max").toString());
                        }

                        if (!arrayE1.isEmpty()){
                            avg_use += Long.valueOf(objE1.get("value_avg").toString());
                            max_use += Long.valueOf(objE1.get("value_min").toString());
                            min_use += Long.valueOf(objE1.get("value_max").toString());
                            avg_total += Long.valueOf(objE2.get("value_avg").toString());
                            max_total += Long.valueOf(objE2.get("value_min").toString());
                            min_total += Long.valueOf(objE2.get("value_max").toString());
                        }

                        if (!arrayF1.isEmpty()){
                            avg_use += Long.valueOf(objF1.get("value_avg").toString());
                            max_use += Long.valueOf(objF1.get("value_min").toString());
                            min_use += Long.valueOf(objF1.get("value_max").toString());
                            avg_total += Long.valueOf(objF2.get("value_avg").toString());
                            max_total += Long.valueOf(objF2.get("value_min").toString());
                            min_total += Long.valueOf(objF2.get("value_max").toString());
                        }
                        if (!arrayG1.isEmpty()){
                            avg_use += Long.valueOf(objG1.get("value_avg").toString());
                            max_use += Long.valueOf(objG1.get("value_min").toString());
                            min_use += Long.valueOf(objG1.get("value_max").toString());
                            avg_total += Long.valueOf(objG2.get("value_avg").toString());
                            max_total += Long.valueOf(objG2.get("value_min").toString());
                            min_total += Long.valueOf(objG2.get("value_max").toString());
                        }


                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(objC1.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        //平均求和除以总磁盘数
                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(100*(double)avg_use/avg_total);
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(100*(double)max_use/max_total);
                        cell.setCellStyle(style);

                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(100*(double)min_use/min_total);
                        //设置单元格样式
                        cell.setCellStyle(style);

                    } else
                        break;
                }
                i = 0;
                sheet = wb.createSheet("磁盘IO速率");
                sheet.setColumnWidth(0, 6000);

                sheet.setColumnWidth(1, 6000);
                sheet.setColumnWidth(2, 6000);

                sheet.setColumnWidth(3, 6000);
                sheet.setColumnWidth(4, 6000);

                sheet.setColumnWidth(5, 6000);
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                //磁盘I/O速率
                row = sheet.createRow(i);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("磁盘I/O速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);



                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最大");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("最小");
                cell.setCellStyle(style_temp);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                i++;

                row = sheet.createRow(i);
                cell = row.createCell(0);
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("读速率(sps)");
                cell.setCellStyle(style_temp);

                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("读速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(4);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("读速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(6);
                // 单元格设值
                cell.setCellValue("写速率(sps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                i++;

                String itemid5 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "perf_counter[\\2\\18]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                String itemid6 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "perf_counter[\\2\\16]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array5 = (JSONArray) monitorService.getTrendByItemid(itemid5).get("result");// 通过监控项获得趋势
                JSONArray array6 = (JSONArray) monitorService.getTrendByItemid(itemid6).get("result");// 通过监控项获得趋势
                array5.sort(Comparator.comparing(obj5 -> {
                    Integer value = ((JSONObject) obj5).getIntValue("clock");
                    return value;
                }).reversed());
                array6.sort(Comparator.comparing(obj6 -> {
                    Integer value = ((JSONObject) obj6).getIntValue("clock");
                    return value;
                }).reversed());

                JSONObject arr_obj5 = (JSONObject) array5.get(0);
                Long num5 = Long.valueOf(arr_obj5.get("clock").toString());
                Iterator iterator6 = array6.iterator();
                for (Iterator iterator5 = array5.iterator(); iterator5.hasNext(); ) {
                    JSONObject obj5 = (JSONObject) iterator5.next();
                    JSONObject obj6 = (JSONObject) iterator6.next();
                    if ((Long.valueOf(obj5.get("clock").toString()) >= start) &&(Long.valueOf(obj5.get("clock").toString()) <= end)) {
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj5.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        cell = row.createCell(2);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_avg").toString());
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        cell = row.createCell(4);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);


                        cell = row.createCell(5);
                        // 单元格设值
                        cell.setCellValue(obj6.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                        cell = row.createCell(6);
                        // 单元格设值
                        cell.setCellValue(obj5.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                    } else
                        continue;
                }
                //windows网络接口流量数据
                List<Map<String,Object>> resultList = monitorService.getNetData(hostids);
                for (int j = 0; j < resultList.size(); j++) {
                    itemid = resultList.get(j).get("itemid").toString();
                    JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                    array7.sort(Comparator.comparing(obj7 -> {
                        Integer value = ((JSONObject) obj7).getIntValue("clock");
                        return value;
                    }).reversed());
                    JSONObject arr_obj7 = (JSONObject) array7.get(0);
                    Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                    if ("0".equals(arr_obj7.get("value_avg").toString()))
                        continue;
                    i = 0;
                    String str = resultList.get(j).get("name").toString();
                    List<String> list=new ArrayList<>();
                    String re = "(?<=\\()(\\S+)(?=\\))";
                    Matcher mat = Pattern.compile(re).matcher(str);
                    while(mat.find()){
                        list.add(mat.group());
                    }
                    String name = "";
                    String type = str.split(":")[1].split(" ")[2];
                    if ("sent".equals(type))
                        name = list.get(0)+" 发送流量";
                    else
                        name = list.get(0)+" 接受流量";
                    sheet = wb.createSheet(name);
                    sheet.setColumnWidth(0, 6000);

                    sheet.setColumnWidth(1, 6000);
                    sheet.setColumnWidth(2, 6000);

                    sheet.setColumnWidth(3, 6000);
                    sheet.setColumnWidth(4, 6000);

                    sheet.setColumnWidth(5, 6000);
                    sheet.setColumnWidth(6, 6000);

                    row = sheet.createRow(i);
                    cell = row.createCell(0);
                    i++;
                    // 单元格设值
                    cell.setCellValue("设备名称");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(resultMap.get(0).get("host").toString());
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("时间：");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue(startTime+"---"+endTime);

                    row = sheet.createRow(i);
                    //合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 6));
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList.get(j).get("name")+"  网络接口流量(kbps)");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    row = sheet.createRow(i);

                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue("时间");
                    cell.setCellStyle(style_temp);

                    cell = row.createCell(1);
                    cell.setCellValue("平均");
                    //设置单元格样式
                    cell.setCellStyle(style_temp);
                    //合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));


                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("最大");

                    cell.setCellStyle(style_temp);
                    //合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));

                    cell = row.createCell(5);
                    // 单元格设值
                    cell.setCellValue("最小");

                    cell.setCellStyle(style_temp);
                    //合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                    i++;


                    for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                        JSONObject obj7 = (JSONObject) iterator7.next();
                        if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                            //合并单元格
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 3, 4));
                            sheet.addMergedRegion(new CellRangeAddress(i, i, 5, 6));
                            row = sheet.createRow(i);
                            i++;
                            cell = row.createCell(0);
                            // 单元格设值
                            cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                            //设置单元格样式
                            cell.setCellStyle(style);

                            cell = row.createCell(1);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_avg").toString());
                            //设置单元格样式
                            cell.setCellStyle(style);

                            cell = row.createCell(3);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_max").toString());
                            //设置单元格样式
                            cell.setCellStyle(style);

                            cell = row.createCell(5);
                            // 单元格设值
                            cell.setCellValue(obj7.get("value_min").toString());
                            //设置单元格样式
                            cell.setCellStyle(style);
                        } else
                            continue;
                    }

                }
//                response.reset();
//                response.addHeader("Access-Control-Allow-Origin", "*");
//                response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
                // 设定保存类型 excel
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                // 保存文件的名称
                String fileName = "性能分析报表";
                try {
                    // 保存文件编码格式
                    String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
                    // 保存文件头部默认数据
                    response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                    // 字节流保存
                    OutputStream os = response.getOutputStream();
                    // 写入
                    wb.write(os);
                    // 刷新缓冲区
                    os.flush();
                    // 关闭字节流
                    os.close();
                }catch (Exception e){
                    e.getMessage();
                }
            }
        }
        // 交换机设备
        // 注释同上类似
        else {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("CPU利用率");
            HSSFRow row = sheet.createRow(0);
            sheet.setColumnWidth(0, 6000);
            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(2, 6000);
            sheet.setColumnWidth(3, 6000);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFCellStyle style_temp = wb.createCellStyle();
            style_temp.setFillForegroundColor((short) 13);
            style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            HSSFCell cell = row.createCell(i);
            // 第0行
            Calendar c = Calendar.getInstance();
            c.add(Calendar.DAY_OF_MONTH, -1);
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//                int todayhours =  ((new Date()).getHours()-1);
            int todayhours =  (c.get(Calendar.HOUR_OF_DAY)-1);
//                int yesterdayhours =  ((new Date()).getHours());
            int yesterdayhours =  (c.get(Calendar.HOUR_OF_DAY));
            String today = df.format(new Date())+"  "+todayhours+":00";
            String yesterday = df.format(c.getTime())+"  "+yesterdayhours+":00";

            row = sheet.createRow(i);
            cell = row.createCell(0);
            i++;
            // 单元格设值
            cell.setCellValue("设备名称");
            //设置单元格样式
            cell.setCellStyle(style_temp);
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(resultMap.get(0).get("host").toString());
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("时间：");
            //设置单元格样式
            cell.setCellStyle(style_temp);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(startTime+"---"+endTime);
            //第1行
            row = sheet.createRow(i);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            //CPU利用率
            cell = row.createCell(0);
            cell.setCellValue("CPU利用率(%)");
            //设置单元格样式
            cell.setCellStyle(style_temp);
            //第2行
            row = sheet.createRow(i);

            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("时间");
            cell.setCellStyle(style_temp);

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("平均");
            //设置单元格样式
            cell.setCellStyle(style_temp);

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("最大");
            //设置单元格样式
            cell.setCellStyle(style_temp);

            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("最小");
            //设置单元格样式
            cell.setCellStyle(style_temp);

            i++;
            itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");
            JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
            array.sort(Comparator.comparing(obj -> {
                Integer value = ((JSONObject) obj).getIntValue("clock");
                return value;
            }).reversed());
            JSONObject arr_obj = (JSONObject) array.get(0);
            Long num = Long.valueOf(arr_obj.get("clock").toString());
            for (Iterator iterator = array.iterator(); iterator.hasNext(); ) {
                JSONObject obj = (JSONObject) iterator.next();
                if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)) {

                    row = sheet.createRow(i);
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                    //设置单元格样式
                    cell.setCellStyle(style);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(obj.get("value_avg").toString());
                    //设置单元格样式
                    cell.setCellStyle(style);

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(obj.get("value_max").toString());
                    cell.setCellStyle(style);

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue(obj.get("value_min").toString());
                    //设置单元格样式
                    cell.setCellStyle(style);
                } else
                    continue;
            }
            i = 0;
            sheet = wb.createSheet("内存利用率");
            sheet.setColumnWidth(0, 6000);

            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(2, 6000);

            sheet.setColumnWidth(3, 6000);
            sheet.setColumnWidth(4, 6000);

            sheet.setColumnWidth(5, 6000);
            sheet.setColumnWidth(6, 6000);
            row = sheet.createRow(i);
            cell = row.createCell(0);
            i++;
            // 单元格设值
            cell.setCellValue("设备名称");
            //设置单元格样式
            cell.setCellStyle(style_temp);
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(resultMap.get(0).get("host").toString());
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("时间：");

            cell.setCellStyle(style_temp);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(startTime+"---"+endTime);
            //内存利用率
            row = sheet.createRow(i);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("内存利用率(%)");

            cell.setCellStyle(style_temp);



            row = sheet.createRow(i);

            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("时间");

            cell.setCellStyle(style_temp);

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("平均");

            cell.setCellStyle(style_temp);


            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("最大");

            cell.setCellStyle(style_temp);


            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("最小");

            cell.setCellStyle(style_temp);

            i++;
            itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.util").get("result")).getJSONObject(0).getString("itemid");
            JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
            array1.sort(Comparator.comparing(obj1 -> {
                Integer value = ((JSONObject) obj1).getIntValue("clock");
                return value;
            }).reversed());
            JSONObject arr_obj1 = (JSONObject) array1.get(0);
            Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
            for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                JSONObject obj1 = (JSONObject) iterator1.next();
                if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                    row = sheet.createRow(i);
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                    cell.setCellStyle(style);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(obj1.get("value_avg").toString());
                    cell.setCellStyle(style);

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(obj1.get("value_max").toString());
                    cell.setCellStyle(style);

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue(obj1.get("value_min").toString());

                    cell.setCellStyle(style);
                } else
                    continue;
            }

            List<Map<String,Object>> resultList = monitorService.getNetData(hostids);
            for (int j = 0; j < resultList.size(); j++) {
                itemid = resultList.get(j).get("itemid").toString();
                JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                array7.sort(Comparator.comparing(obj7 -> {
                    Integer value = ((JSONObject) obj7).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj7 = (JSONObject) array7.get(0);
                Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                if ("0".equals(arr_obj7.get("value_avg").toString()))
                    continue;
                i = 0;
                String str = resultList.get(j).get("name").toString();
                String name = str.split(":")[0].split(" ")[1].replace("/","_");
                String type = str.split(":")[1].split(" ")[2];
                if ("sent".equals(type))
                    name = name+" 发送流量";
                else
                    name = name+" 接受流量";
                sheet = wb.createSheet(name);
                sheet.setColumnWidth(0, 6000);

                sheet.setColumnWidth(1, 6000);
                sheet.setColumnWidth(2, 6000);

                sheet.setColumnWidth(3, 6000);
                sheet.setColumnWidth(4, 6000);

                sheet.setColumnWidth(5, 6000);
                sheet.setColumnWidth(6, 6000);
                row = sheet.createRow(i);
                cell = row.createCell(0);
                i++;
                // 单元格设值
                cell.setCellValue("设备名称");
                cell.setCellStyle(style_temp);
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultMap.get(0).get("host").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("时间：");
                cell.setCellStyle(style_temp);
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(startTime+"---"+endTime);
                row = sheet.createRow(i);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("name")+"  网络接口流量(kbps)");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                row = sheet.createRow(i);

                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("时间");
                //设置单元格样式
                cell.setCellStyle(style_temp);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("平均");
                //设置单元格样式
                cell.setCellStyle(style_temp);


                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("最大");
                cell.setCellStyle(style_temp);

                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("最小");
                //设置单元格样式
                cell.setCellStyle(style_temp);
                i++;


                for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                    JSONObject obj7 = (JSONObject) iterator7.next();
                    if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                        row = sheet.createRow(i);
                        i++;
                        cell = row.createCell(0);
                        // 单元格设值
                        cell.setCellValue(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(1);
                        // 单元格设值
                        cell.setCellValue(obj7.get("value_avg").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(2);
                        // 单元格设值
                        cell.setCellValue(obj7.get("value_max").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);

                        cell = row.createCell(3);
                        // 单元格设值
                        cell.setCellValue(obj7.get("value_min").toString());
                        //设置单元格样式
                        cell.setCellStyle(style);
                    } else
                        continue;
                }

            }
//            response.reset();
//            response.addHeader("Access-Control-Allow-Origin", "*");
//            response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            String fileName = "性能分析报表";
            try {
                String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.flush();
                os.close();
                return true;
            } catch (Exception e) {
                e.getMessage();
                return false;
            }
        }
        return true;
    }

    /**
     * 获得选中事件的监控项信息
     * @param hostids
     * @param startTime
     * @param endTime
     * @return
     */
    @Override
    public List<List> getItemDataInfo_select_event(String hostids,String startTime,String endTime ) {
        int flag = 1;
        String status = "";
        Integer hostType = 0;
        // 将ms级别的时间戳转化为年月日时分秒
        long start = (DateUtils.timeToStamp(startTime))/1000;
        long end = (DateUtils.timeToStamp(endTime))/1000;

        try{
            int tmpj=0;
            SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Calendar c = Calendar.getInstance();
            Calendar c1 = Calendar.getInstance();
            String s1 = sf.format(new Date(start*1000));
            String e1 = sf.format(new Date(end*1000));
            Date st = sf.parse(s1);
            Date en = sf.parse(e1);


        List<Map<String,Object>> hostList = getAllOnlineHost(hostids);
        if ("1".equals(hostList.get(0).get("available")))
            hostType = 0;
        if ("1".equals(hostList.get(0).get("snmp_available")))
            hostType = 1;
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        List<Map<String,Object>> resultMap = monitorService.getItemDataInfo(hostids,status,hostType);   // 获得监控项信息
        flag = monitorService.getItemsByHostID(hostids);
        List<List> resultFinal = new ArrayList<>();
        int i = 0;
        String itemid;
        if (hostType == 0){
            // Linux
            if (flag == 1){
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                if (array.isEmpty())
                    System.out.println("我是空，我是新的");
                array.sort(Comparator.comparing(obj -> {
                    Integer value = ((JSONObject) obj).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj = (JSONObject) array.get(0);
                Long num = Long.valueOf(arr_obj.get("clock").toString());
                List<List> resultList = new ArrayList<>();
                List<String> cpuName = new ArrayList<String>(){{
                   add("CPU利用率");
                }};
                resultList.add(cpuName);
                for(Iterator iterator = array.iterator();iterator.hasNext();){
                    JSONObject obj = (JSONObject) iterator.next();
                    if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)){
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                        resultTemp.add(obj.get("value_min").toString());
                        resultTemp.add(obj.get("value_avg").toString());
                        resultTemp.add(obj.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }


                //cpu使用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> memeryName = new ArrayList<String>(){{
                    add("内存利用率");
                }};
                resultList.add(memeryName);
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.utilization").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                array1.sort(Comparator.comparing(obj1 -> {
                    Integer value = ((JSONObject) obj1).getIntValue("clock");// 通过clock排序
                    return value;
                }).reversed());
                JSONObject arr_obj1 = (JSONObject) array1.get(0);
                Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
                for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                    JSONObject obj1 = (JSONObject) iterator1.next();
                    if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                        resultTemp.add(obj1.get("value_min").toString());
                        resultTemp.add(obj1.get("value_avg").toString());
                        resultTemp.add(obj1.get("value_max").toString());
                        resultList.add(resultTemp);
                        }
                    }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //内存使用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> diskName = new ArrayList<String>(){{
                    add("磁盘利用率");
                }};
                resultList.add(diskName);
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[/,used]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                String itemid3 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[/,total]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array2 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                JSONArray array3 = (JSONArray) monitorService.getTrendByItemid(itemid3).get("result");// 通过监控项获得趋势
                array2.sort(Comparator.comparing(obj2 -> {
                    Integer value = ((JSONObject) obj2).getIntValue("clock");
                    return value;
                }).reversed());
                array3.sort(Comparator.comparing(obj3 -> {
                    Integer value = ((JSONObject) obj3).getIntValue("clock");
                    return value;
                }).reversed());

                JSONObject arr_obj2 = (JSONObject) array2.get(0);
                Long num2 = Long.valueOf(arr_obj2.get("clock").toString());
                Iterator iterator3 = array3.iterator();
                for (Iterator iterator2 = array2.iterator(); iterator2.hasNext(); ) {
                    JSONObject obj2 = (JSONObject) iterator2.next();
                    JSONObject obj3 = (JSONObject) iterator3.next();
                    if ((Long.valueOf(obj2.get("clock").toString()) >= start) &&(Long.valueOf(obj2.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj2.get("clock").toString()));
                        resultTemp.add(100*(double)Long.parseLong(obj2.get("value_min").toString())/Long.parseLong(obj3.get("value_min").toString()));
                        resultTemp.add(100*(double)Long.parseLong(obj2.get("value_avg").toString())/Long.parseLong(obj3.get("value_avg").toString()));
                        resultTemp.add(100*(double)Long.parseLong(obj2.get("value_max").toString())/Long.parseLong(obj3.get("value_max").toString()));
                        resultList.add(resultTemp);
                    }
                }

                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }

                //封装磁盘利用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> ioReadName = new ArrayList<String>(){{
                    add("I/O读速率");
                }};
                resultList.add(ioReadName);
                String itemid5 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.dev.write[,,]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                String itemid6 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.dev.read[,,]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array5 = (JSONArray) monitorService.getTrendByItemid(itemid5).get("result");// 通过监控项获得趋势
                JSONArray array6 = (JSONArray) monitorService.getTrendByItemid(itemid6).get("result");// 通过监控项获得趋势
                array5.sort(Comparator.comparing(obj5 -> {
                    Integer value = ((JSONObject) obj5).getIntValue("clock");
                    return value;
                }).reversed());
                array6.sort(Comparator.comparing(obj6 -> {
                    Integer value = ((JSONObject) obj6).getIntValue("clock");
                    return value;
                }).reversed());

                JSONObject arr_obj5 = (JSONObject) array5.get(0);
                Long num5 = Long.valueOf(arr_obj5.get("clock").toString());
                for (Iterator iterator6 = array6.iterator(); iterator6.hasNext(); ) {
                    JSONObject obj6 = (JSONObject) iterator6.next();
                    if ((Long.valueOf(obj6.get("clock").toString()) >= start) &&(Long.valueOf(obj6.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj6.get("clock").toString()));
                        resultTemp.add(obj6.get("value_min").toString());
                        resultTemp.add(obj6.get("value_avg").toString());
                        resultTemp.add(obj6.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }

                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                // IO读速率
                resultList = new ArrayList<>();

                List<String> ioWriteName = new ArrayList<String>(){{
                    add("I/O写速率");
                }};
                resultList.add(ioWriteName);
                for (Iterator iterator5 = array5.iterator(); iterator5.hasNext(); ) {
                    JSONObject obj5 = (JSONObject) iterator5.next();
                    if ((Long.valueOf(obj5.get("clock").toString()) >= start) &&(Long.valueOf(obj5.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj5.get("clock").toString()));
                        resultTemp.add(obj5.get("value_min").toString());
                        resultTemp.add(obj5.get("value_avg").toString());
                        resultTemp.add(obj5.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }

                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //IO写速率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();


                List<Map<String,Object>> result = monitorService.getNetData(hostids);   //获得设备网络数据
                for (int j = 0; j < result.size(); j++) {
                    itemid = result.get(j).get("itemid").toString();
                    JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                    array7.sort(Comparator.comparing(obj7 -> {
                        Integer value = ((JSONObject) obj7).getIntValue("clock");
                        return value;
                    }).reversed());
                    JSONObject arr_obj7 = (JSONObject) array7.get(0);
                    Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                    if ("0".equals(arr_obj7.get("value_avg").toString()))
                        continue;
                    String str = result.get(j).get("name").toString();
                    String name = str.split(":")[0].substring(10);
                    String type = str.split(":")[1].split(" ")[2];
                    // 判定网络数据 发送/接受 流量
                    if ("sent".equals(type))
                        name = name+" 发送流量";
                    else
                        name = name+" 接受流量";
                    String finalName = name;
                    List<String> netName = new ArrayList<String>(){{
                        add(finalName);
                    }};
                    resultList.add(netName);
                    for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                        JSONObject obj7 = (JSONObject) iterator7.next();
                        if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                            List resultTemp = new ArrayList();
                            resultTemp.add(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                            resultTemp.add(obj7.get("value_min").toString());
                            resultTemp.add(obj7.get("value_avg").toString());
                            resultTemp.add(obj7.get("value_max").toString());
                            resultList.add(resultTemp);
                        }
                    }
                    c.setTime(st);
                    c1.setTime(en);
                    c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                    c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                    c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                    c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                    while (!c.equals(c1)){
                        c1.add(Calendar.MINUTE,-60);
                        Date date=c1.getTime();
                        String ss=sf.format(date);
                        tmpj=0;
                        for (; tmpj < resultList.size(); tmpj++) {
                            if (ss.equals(resultList.get(tmpj).get(0))){
                                break;
                            }
                        }
                        if (tmpj==resultList.size()){
                            List resultTemp = new ArrayList();
                            resultTemp.add(ss);
                            resultTemp.add("0");
                            resultTemp.add("0");
                            resultTemp.add("0");
                            resultList.add(resultTemp);
                        }
                    }
                    Collections.reverse(resultList);
                    resultList.add(0,resultList.remove(resultList.size()-1));
                    resultFinal.add(resultList);
                    resultList = new ArrayList<>();
                }
                return resultFinal;
            }
            // Windows
            else {
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                array.sort(Comparator.comparing(obj -> {
                    Integer value = ((JSONObject) obj).getIntValue("clock");// 通过clock排序
                    return value;
                }).reversed());
                JSONObject arr_obj = (JSONObject) array.get(0);
                Long num = Long.valueOf(arr_obj.get("clock").toString());
                List<List> resultList = new ArrayList<>();
                List<String> cpuName = new ArrayList<String>(){{
                    add("CPU利用率");
                }};
                resultList.add(cpuName);
                for (Iterator iterator = array.iterator(); iterator.hasNext(); ) {
                    JSONObject obj = (JSONObject) iterator.next();
                    if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                        resultTemp.add(obj.get("value_min").toString());
                        resultTemp.add(obj.get("value_avg").toString());
                        resultTemp.add(obj.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //封装cpu使用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> memeryName = new ArrayList<String>(){{
                    add("内存利用率");
                }};
                resultList.add(memeryName);
                itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
                array1.sort(Comparator.comparing(obj1 -> {
                    Integer value = ((JSONObject) obj1).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj1 = (JSONObject) array1.get(0);
                Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
                for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                    JSONObject obj1 = (JSONObject) iterator1.next();
                    if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                        resultTemp.add(obj1.get("value_min").toString());
                        resultTemp.add(obj1.get("value_avg").toString());
                        resultTemp.add(obj1.get("value_max").toString());
                        resultList.add(resultTemp);
                        }
                    }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //封装内存使用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> diskName = new ArrayList<String>(){{
                    add("磁盘利用率");
                }};
                resultList.add(diskName);
                //初始化变量
                String itemidC1="",itemidC2="",itemidD1="",itemidD2="",itemidE1="",itemidE2="",itemidF1="",itemidF2="",itemidG1="",itemidG2="";
                JSONArray arrayC1=null,arrayC2=null,arrayD1=null,arrayD2=null,arrayE1=null,arrayE2=null,arrayF1=null,arrayF2=null,arrayG1=null,arrayG2=null;
                JSONArray arrC1=null,arrC2=null,arrD1=null,arrD2=null,arrE1=null,arrE2=null,arrF1=null,arrF2=null,arrG1=null,arrG2=null;
                Long numFinal = 0l;
                int count = 0;

                // C盘
                arrayC1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[C:,used]").get("result"));
                if (!arrayC1.isEmpty()){
                    count++;
                    arrayC2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[C:,total]").get("result"));
                    itemidC1 = arrayC1.getJSONObject(0).getString("itemid");
                    itemidC2 = arrayC2.getJSONObject(0).getString("itemid");
                    arrC1 = (JSONArray) monitorService.getTrendByItemid(itemidC1).get("result");
                    arrC1.sort(Comparator.comparing(objC1 -> {
                        Integer value = ((JSONObject) objC1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrC2 = (JSONArray) monitorService.getTrendByItemid(itemidC2).get("result");
                    arrC2.sort(Comparator.comparing(objC2 -> {
                        Integer value = ((JSONObject) objC2).getIntValue("clock");
                        return value;
                    }).reversed());
                    numFinal = Long.valueOf(((JSONObject) arrC1.get(0)).get("clock").toString());
                }

                //D盘
                arrayD1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[D:,used]").get("result"));
                if (!arrayD1.isEmpty()){
                    count++;
                    arrayD2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[D:,total]").get("result"));
                    itemidD1 = arrayD1.getJSONObject(0).getString("itemid");
                    itemidD2 = arrayD2.getJSONObject(0).getString("itemid");
                    arrD1 = (JSONArray) monitorService.getTrendByItemid(itemidD1).get("result");
                    arrD1.sort(Comparator.comparing(objD1 -> {
                        Integer value = ((JSONObject) objD1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrD2 = (JSONArray) monitorService.getTrendByItemid(itemidD2).get("result");
                    arrD2.sort(Comparator.comparing(objD2 -> {
                        Integer value = ((JSONObject) objD2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                //E盘
                arrayE1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[E:,used]").get("result"));
                if (!arrayE1.isEmpty()){
                    count++;
                    arrayE2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[E:,total]").get("result"));
                    itemidE1 = arrayE1.getJSONObject(0).getString("itemid");
                    itemidE2 = arrayE2.getJSONObject(0).getString("itemid");
                    arrE1 = (JSONArray) monitorService.getTrendByItemid(itemidE1).get("result");
                    arrE1.sort(Comparator.comparing(objE1 -> {
                        Integer value = ((JSONObject) objE1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrE2 = (JSONArray) monitorService.getTrendByItemid(itemidE2).get("result");
                    arrE2.sort(Comparator.comparing(objE2 -> {
                        Integer value = ((JSONObject) objE2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                // F盘
                arrayF1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[F:,used]").get("result"));
                if (!arrayF1.isEmpty()){
                    count++;
                    arrayF2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[F:,total]").get("result"));
                    itemidF1 = arrayF1.getJSONObject(0).getString("itemid");
                    itemidF2 = arrayF2.getJSONObject(0).getString("itemid");
                    arrF1 = (JSONArray) monitorService.getTrendByItemid(itemidF1).get("result");
                    arrF1.sort(Comparator.comparing(objF1 -> {
                        Integer value = ((JSONObject) objF1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrF2 = (JSONArray) monitorService.getTrendByItemid(itemidF2).get("result");
                    arrF2.sort(Comparator.comparing(objF2 -> {
                        Integer value = ((JSONObject) objF2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                //G盘
                arrayG1 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[G:,used]").get("result"));
                if (!arrayG1.isEmpty()){
                    count++;
                    arrayG2 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vfs.fs.size[G:,total]").get("result"));
                    itemidG1 = arrayG1.getJSONObject(0).getString("itemid");
                    itemidG2 = arrayG2.getJSONObject(0).getString("itemid");
                    arrG1 = (JSONArray) monitorService.getTrendByItemid(itemidG1).get("result");
                    arrG1.sort(Comparator.comparing(objG1 -> {
                        Integer value = ((JSONObject) objG1).getIntValue("clock");
                        return value;
                    }).reversed());
                    arrG2 = (JSONArray) monitorService.getTrendByItemid(itemidG2).get("result");
                    arrG2.sort(Comparator.comparing(objG2 -> {
                        Integer value = ((JSONObject) objG2).getIntValue("clock");
                        return value;
                    }).reversed());
                }

                // 盘不为空时获得数据
                Iterator iteratorC2 = arrC2.iterator();

                Iterator iteratorD1 = arrC1.iterator();
                Iterator iteratorD2 = arrC2.iterator();
                if (!arrayD1.isEmpty()){
                    iteratorD1 = arrD1.iterator();
                    iteratorD2 = arrD2.iterator();
                }

                Iterator iteratorE1 = arrC1.iterator();
                Iterator iteratorE2 = arrC2.iterator();
                if (!arrayE1.isEmpty()){
                    iteratorE1 = arrE1.iterator();
                    iteratorE2 = arrE2.iterator();
                }

                Iterator iteratorF1 = arrC1.iterator();
                Iterator iteratorF2 = arrC2.iterator();
                if (!arrayF1.isEmpty()){
                    iteratorF1 = arrF1.iterator();
                    iteratorF2 = arrF2.iterator();
                }

                Iterator iteratorG1 = arrC1.iterator();
                Iterator iteratorG2 = arrC2.iterator();
                if (!arrayG1.isEmpty()){
                    iteratorG1 = arrG1.iterator();
                    iteratorG2 = arrG2.iterator();
                }
                JSONObject objD1 = null;
                JSONObject objD2 = null;
                if (!arrayD1.isEmpty()) {
                    objD1 = (JSONObject) iteratorD1.next();
                    objD2 = (JSONObject) iteratorD2.next();
                }

                JSONObject objE1 = null;
                JSONObject objE2 = null;
                if (!arrayE1.isEmpty()) {
                    objE1 = (JSONObject) iteratorE1.next();
                    objE2 = (JSONObject) iteratorE2.next();
                }

                JSONObject objF1 = null;
                JSONObject objF2 = null;
                if (!arrayF1.isEmpty()) {
                    objF1 = (JSONObject) iteratorF1.next();
                    objF2 = (JSONObject) iteratorF2.next();
                }

                JSONObject objG1 = null;
                JSONObject objG2 = null;
                if (!arrayG1.isEmpty()) {
                    objG1 = (JSONObject) iteratorG1.next();
                    objG2 = (JSONObject) iteratorG2.next();
                }

                // 对不为空的盘数据求和
                for (Iterator iteratorC1 = arrC1.iterator(); iteratorC1.hasNext(); ) {
                    Long avg_use = 0l , max_use = 0l , min_use = 0l;
                    Long avg_total = 0l , max_total = 0l , min_total = 0l;
                    JSONObject objC1 = (JSONObject) iteratorC1.next();
                    JSONObject objC2 = (JSONObject) iteratorC2.next();

                    if ((Long.valueOf(objC1.get("clock").toString()) >= start) &&(Long.valueOf(objC1.get("clock").toString()) <= end)) {
                        if (!arrayC1.isEmpty()){
                            avg_use += Long.valueOf(objC1.get("value_avg").toString());
                            max_use += Long.valueOf(objC1.get("value_min").toString());
                            min_use += Long.valueOf(objC1.get("value_max").toString());
                            avg_total += Long.valueOf(objC2.get("value_avg").toString());
                            max_total += Long.valueOf(objC2.get("value_min").toString());
                            min_total += Long.valueOf(objC2.get("value_max").toString());
                        }

                        if (!arrayD1.isEmpty()){
                            avg_use += Long.valueOf(objD1.get("value_avg").toString());
                            max_use += Long.valueOf(objD1.get("value_min").toString());
                            min_use += Long.valueOf(objD1.get("value_max").toString());
                            avg_total += Long.valueOf(objD2.get("value_avg").toString());
                            max_total += Long.valueOf(objD2.get("value_min").toString());
                            min_total += Long.valueOf(objD2.get("value_max").toString());
                        }

                        if (!arrayE1.isEmpty()){
                            avg_use += Long.valueOf(objE1.get("value_avg").toString());
                            max_use += Long.valueOf(objE1.get("value_min").toString());
                            min_use += Long.valueOf(objE1.get("value_max").toString());
                            avg_total += Long.valueOf(objE2.get("value_avg").toString());
                            max_total += Long.valueOf(objE2.get("value_min").toString());
                            min_total += Long.valueOf(objE2.get("value_max").toString());
                        }

                        if (!arrayF1.isEmpty()){
                            avg_use += Long.valueOf(objF1.get("value_avg").toString());
                            max_use += Long.valueOf(objF1.get("value_min").toString());
                            min_use += Long.valueOf(objF1.get("value_max").toString());
                            avg_total += Long.valueOf(objF2.get("value_avg").toString());
                            max_total += Long.valueOf(objF2.get("value_min").toString());
                            min_total += Long.valueOf(objF2.get("value_max").toString());
                        }
                        if (!arrayG1.isEmpty()){
                            avg_use += Long.valueOf(objG1.get("value_avg").toString());
                            max_use += Long.valueOf(objG1.get("value_min").toString());
                            min_use += Long.valueOf(objG1.get("value_max").toString());
                            avg_total += Long.valueOf(objG2.get("value_avg").toString());
                            max_total += Long.valueOf(objG2.get("value_min").toString());
                            min_total += Long.valueOf(objG2.get("value_max").toString());
                        }
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(objC1.get("clock").toString()));
                        resultTemp.add(100*(double)min_use/min_total);
                        resultTemp.add(100*(double)avg_use/avg_total);
                        resultTemp.add(100*(double)max_use/max_total);
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
//                封装磁盘使用率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> ioReadName = new ArrayList<String>(){{
                    add("I/O读速率");
                }};
                resultList.add(ioReadName);
                String itemid5 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "perf_counter[\\2\\18]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                String itemid6 = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "perf_counter[\\2\\16]").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
                JSONArray array5 = (JSONArray) monitorService.getTrendByItemid(itemid5).get("result");// 通过监控项获得趋势
                JSONArray array6 = (JSONArray) monitorService.getTrendByItemid(itemid6).get("result");// 通过监控项获得趋势
                array5.sort(Comparator.comparing(obj5 -> {
                    Integer value = ((JSONObject) obj5).getIntValue("clock");
                    return value;
                }).reversed());
                array6.sort(Comparator.comparing(obj6 -> {
                    Integer value = ((JSONObject) obj6).getIntValue("clock");
                    return value;
                }).reversed());

                JSONObject arr_obj5 = (JSONObject) array5.get(0);
                Long num5 = Long.valueOf(arr_obj5.get("clock").toString());
                for (Iterator iterator6 = array6.iterator(); iterator6.hasNext(); ) {
                    JSONObject obj6 = (JSONObject) iterator6.next();
                    if ((Long.valueOf(obj6.get("clock").toString()) >= start) &&(Long.valueOf(obj6.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj6.get("clock").toString()));
                        resultTemp.add(obj6.get("value_min").toString());
                        resultTemp.add(obj6.get("value_avg").toString());
                        resultTemp.add(obj6.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //封装磁盘读速率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
                List<String> ioWriteName = new ArrayList<String>(){{
                    add("I/O写速率");
                }};
                resultList.add(ioWriteName);
                for (Iterator iterator5 = array5.iterator(); iterator5.hasNext(); ) {
                    JSONObject obj5 = (JSONObject) iterator5.next();
                    if ((Long.valueOf(obj5.get("clock").toString()) >= start) &&(Long.valueOf(obj5.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj5.get("clock").toString()));
                        resultTemp.add(obj5.get("value_min").toString());
                        resultTemp.add(obj5.get("value_avg").toString());
                        resultTemp.add(obj5.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //封装磁盘写速率
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();


                List<Map<String,Object>> result = monitorService.getNetData(hostids);
                for (int j = 0; j < result.size(); j++) {
                    itemid = result.get(j).get("itemid").toString();
                    JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                    array7.sort(Comparator.comparing(obj7 -> {
                        Integer value = ((JSONObject) obj7).getIntValue("clock");
                        return value;
                    }).reversed());
                    JSONObject arr_obj7 = (JSONObject) array7.get(0);
                    Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                    if ("0".equals(arr_obj7.get("value_avg").toString()))
                        continue;
                    String str = result.get(j).get("name").toString();
                    String name = str.split(":")[0].substring(10);
                    String type = str.split(":")[1].split(" ")[2];
                    if ("sent".equals(type))
                        name = name+" 发送流量";
                    else
                        name = name+" 接受流量";
                    String finalName = name;
                    List<String> netName = new ArrayList<String>(){{
                        add(finalName);
                    }};
                    resultList.add(netName);
                    for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                        JSONObject obj7 = (JSONObject) iterator7.next();
                        if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                            List resultTemp = new ArrayList();
                            resultTemp.add(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                            resultTemp.add(obj7.get("value_min").toString());
                            resultTemp.add(obj7.get("value_avg").toString());
                            resultTemp.add(obj7.get("value_max").toString());
                            resultList.add(resultTemp);
                        }
                    }
                    c.setTime(st);
                    c1.setTime(en);
                    c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                    c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                    c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                    c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                    while (!c.equals(c1)){
                        c1.add(Calendar.MINUTE,-60);
                        Date date=c1.getTime();
                        String ss=sf.format(date);
                        tmpj=0;
                        for (; tmpj < resultList.size(); tmpj++) {
                            if (ss.equals(resultList.get(tmpj).get(0))){
                                break;
                            }
                        }
                        if (tmpj==resultList.size()){
                            List resultTemp = new ArrayList();
                            resultTemp.add(ss);
                            resultTemp.add("0");
                            resultTemp.add("0");
                            resultTemp.add("0");
                            resultList.add(resultTemp);
                        }
                    }
                    Collections.reverse(resultList);
                    resultList.add(0,resultList.remove(resultList.size()-1));
                    resultFinal.add(resultList);
                    resultList = new ArrayList<>();
                }
                return resultFinal;
            }
        }
        //网络设备 注释同上类似
        else{
            List<List> resultList = new ArrayList<>();
            List<String> cpuName = new ArrayList<String>(){{
                add("CPU利用率");
            }};
            resultList.add(cpuName);
            itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "system.cpu.util").get("result")).getJSONObject(0).getString("itemid");
            JSONArray array = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
            array.sort(Comparator.comparing(obj -> {
                Integer value = ((JSONObject) obj).getIntValue("clock");
                return value;
            }).reversed());
            JSONObject arr_obj = (JSONObject) array.get(0);
            Long num = Long.valueOf(arr_obj.get("clock").toString());
            for (Iterator iterator = array.iterator(); iterator.hasNext(); ) {
                JSONObject obj = (JSONObject) iterator.next();
                if ((Long.valueOf(obj.get("clock").toString()) >= start) &&(Long.valueOf(obj.get("clock").toString()) <= end)) {
                    List resultTemp = new ArrayList();
                    resultTemp.add(DateUtils.SecondTimeStamp2Date(obj.get("clock").toString()));
                    resultTemp.add(obj.get("value_min").toString());
                    resultTemp.add(obj.get("value_avg").toString());
                    resultTemp.add(obj.get("value_max").toString());
                    resultList.add(resultTemp);
                }
            }
            c.setTime(st);
            c1.setTime(en);
            c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
            c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
            c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
            c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
            while (!c.equals(c1)){
                c1.add(Calendar.MINUTE,-60);
                Date date=c1.getTime();
                String ss=sf.format(date);
                tmpj=0;
                for (; tmpj < resultList.size(); tmpj++) {
                    if (ss.equals(resultList.get(tmpj).get(0))){
                        break;
                    }
                }
                if (tmpj==resultList.size()){
                    List resultTemp = new ArrayList();
                    resultTemp.add(ss);
                    resultTemp.add("0");
                    resultTemp.add("0");
                    resultTemp.add("0");
                    resultList.add(resultTemp);
                }
            }
            //封装cpu利用率
            Collections.reverse(resultList);
            resultList.add(0,resultList.remove(resultList.size()-1));
            resultFinal.add(resultList);
            resultList = new ArrayList<>();
            List<String> memeryName = new ArrayList<String>(){{
                add("内存利用率");
            }};
            resultList.add(memeryName);
            itemid = ((JSONArray) monitorService.getItemByHostidAndKey(hostids, "vm.memory.util").get("result")).getJSONObject(0).getString("itemid");//通过设备hostid何键值获得监控项
            JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");// 通过监控项获得趋势
            array1.sort(Comparator.comparing(obj1 -> {
                Integer value = ((JSONObject) obj1).getIntValue("clock");
                return value;
            }).reversed());
            JSONObject arr_obj1 = (JSONObject) array1.get(0);
            Long num1 = Long.valueOf(arr_obj1.get("clock").toString());
            for (Iterator iterator1 = array1.iterator(); iterator1.hasNext(); ) {
                JSONObject obj1 = (JSONObject) iterator1.next();
                if ((Long.valueOf(obj1.get("clock").toString()) >= start) &&(Long.valueOf(obj1.get("clock").toString()) <= end)) {
                    List resultTemp = new ArrayList();
                    resultTemp.add(DateUtils.SecondTimeStamp2Date(obj1.get("clock").toString()));
                    resultTemp.add(obj1.get("value_min").toString());
                    resultTemp.add(obj1.get("value_avg").toString());
                    resultTemp.add(obj1.get("value_max").toString());
                    resultList.add(resultTemp);
                }
            }
            c.setTime(st);
            c1.setTime(en);
            c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
            c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
            c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
            c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
            while (!c.equals(c1)){
                c1.add(Calendar.MINUTE,-60);
                Date date=c1.getTime();
                String ss=sf.format(date);
                tmpj=0;
                for (; tmpj < resultList.size(); tmpj++) {
                    if (ss.equals(resultList.get(tmpj).get(0))){
                        break;
                    }
                }
                if (tmpj==resultList.size()){
                    List resultTemp = new ArrayList();
                    resultTemp.add(ss);
                    resultTemp.add("0");
                    resultTemp.add("0");
                    resultTemp.add("0");
                    resultList.add(resultTemp);
                }
            }
            //封装内存使用率
            Collections.reverse(resultList);
            resultList.add(0,resultList.remove(resultList.size()-1));
            resultFinal.add(resultList);
            resultList = new ArrayList<>();

            List<Map<String,Object>> result = monitorService.getNetData(hostids);
            for (int j = 0; j < result.size(); j++) {
                itemid = result.get(j).get("itemid").toString();
                JSONArray array7 = (JSONArray) monitorService.getTrendByItemid(itemid).get("result");
                array7.sort(Comparator.comparing(obj7 -> {
                    Integer value = ((JSONObject) obj7).getIntValue("clock");
                    return value;
                }).reversed());
                JSONObject arr_obj7 = (JSONObject) array7.get(0);
                Long num7 = Long.valueOf(arr_obj7.get("clock").toString());
                if ("0".equals(arr_obj7.get("value_avg").toString()))
                    continue;
                String str = result.get(j).get("name").toString();
                String name = str.split(":")[0].substring(10);
                String type = str.split(":")[1].split(" ")[2];
                if ("sent".equals(type))
                    name = name+" 发送流量";
                else
                    name = name+" 接受流量";
                String finalName = name;
                List<String> netName = new ArrayList<String>(){{
                    add(finalName);
                }};
                resultList.add(netName);
                for (Iterator iterator7 = array7.iterator(); iterator7.hasNext(); ) {
                    JSONObject obj7 = (JSONObject) iterator7.next();
                    if ((Long.valueOf(obj7.get("clock").toString()) >= start) &&(Long.valueOf(obj7.get("clock").toString()) <= end)) {
                        List resultTemp = new ArrayList();
                        resultTemp.add(DateUtils.SecondTimeStamp2Date(obj7.get("clock").toString()));
                        resultTemp.add(obj7.get("value_min").toString());
                        resultTemp.add(obj7.get("value_avg").toString());
                        resultTemp.add(obj7.get("value_max").toString());
                        resultList.add(resultTemp);
                    }
                }
                c.setTime(st);
                c1.setTime(en);
                c.add(Calendar.MINUTE,-60-c.get(Calendar.MINUTE));
                c.add(Calendar.SECOND,-c.get(Calendar.SECOND));
                c1.add(Calendar.MINUTE,-60-c1.get(Calendar.MINUTE));
                c1.add(Calendar.SECOND,-c1.get(Calendar.SECOND));
                while (!c.equals(c1)){
                    c1.add(Calendar.MINUTE,-60);
                    Date date=c1.getTime();
                    String ss=sf.format(date);
                    tmpj=0;
                    for (; tmpj < resultList.size(); tmpj++) {
                        if (ss.equals(resultList.get(tmpj).get(0))){
                            break;
                        }
                    }
                    if (tmpj==resultList.size()){
                        List resultTemp = new ArrayList();
                        resultTemp.add(ss);
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultTemp.add("0");
                        resultList.add(resultTemp);
                    }
                }
                //封装网络流量
                Collections.reverse(resultList);
                resultList.add(0,resultList.remove(resultList.size()-1));
                resultFinal.add(resultList);
                resultList = new ArrayList<>();
            }
            return resultFinal;
        }
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 巡检报告各信息
     * @param status
     * @param hostType
     * @param response
     * @return
     */
    @Override
    public Boolean InspectionData(String status, Integer hostType, HttpServletResponse response) {
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        final Integer count = monitorService.getHostCountByTypeAndStatus(hostType,status);  //通过类型获得设备数量
        List<Map<String,Object>> resultList = monitorService.getHostListAvailClassify(1,count,status,hostType); //获得可用设备列表
        // 同最上面的方法定义一个新的excel
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 1200);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = row.createCell(0);
        row.createCell(1);
        sheet.setColumnWidth(1, 4000);
        row.createCell(2);
        sheet.setColumnWidth(2, 4000);
        row.createCell(3);
        sheet.setColumnWidth(3, 4000);
        row.createCell(4);
        sheet.setColumnWidth(4, 4000);
        row.createCell(5);
        sheet.setColumnWidth(5, 4000);
        row.createCell(6);
        sheet.setColumnWidth(6, 6000);
        row.createCell(7);
        sheet.setColumnWidth(7, 4000);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));
        // 单元格设值
        cell.setCellValue("设备日常巡检报告");
        HSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 36);
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置单元格样式
        cell.setCellStyle(style);

        style = wb.createCellStyle();
        font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short)11);
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        HSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) 600);
        HSSFRow row2 = sheet.createRow(2);
        row2.setHeight((short) 600);

        HSSFCell cell1 = row1.createCell(0);
        HSSFCell cell2 = row2.createCell(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));
        cell1 = row1.createCell(1);
        cell2 = row2.createCell(1);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));
        // 单元格设值
        cell1.setCellValue("设备型号");
        //设置单元格样式
        cell1.setCellStyle(style);


        HSSFCell cell3 = row1.createCell(2);
        HSSFCell cell4 = row1.createCell(3);
        HSSFCell cell5 = row1.createCell(4);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1,1,2,4));
        // 单元格设值
        cell3.setCellValue("运行状态检查");
        //设置单元格样式
        cell3.setCellStyle(style);



        cell3 = row2.createCell(2);
        cell4 = row2.createCell(3);
        cell5 = row2.createCell(4);
        // 单元格设值
        cell3.setCellValue("CPU利用率(%)");
        cell4.setCellValue("内存利用率(%)");
        cell5.setCellValue("磁盘状态");
        //设置单元格样式
        cell3.setCellStyle(style);
        cell4.setCellStyle(style);
        cell5.setCellStyle(style);

        CellStyle cs = wb.createCellStyle();
        cs.setFont(font);
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setWrapText(true);
        CellStyle cs1 = wb.createCellStyle();
        cs1.setFont(font);
        cs1.setWrapText(true);
        cell3 = row1.createCell(5);
        // 单元格设值
        cell3.setCellValue("基本安全\n检查");
        //设置单元格样式
        cell3.setCellStyle(cs);
        cell4 = row2.createCell(5);
        // 单元格设值
        cell4.setCellValue("登录、系\n统安全");
        cell4.setCellStyle(cs);


        cell3 = row1.createCell(6);
        // 单元格设值
        cell3.setCellValue("硬件检查");
        cell3.setCellStyle(style);

        cell4 = row2.createCell(6);
        // 单元格设值
        cell4.setCellValue("指示灯、电源、风扇情况");
        //设置单元格样式
        cell4.setCellStyle(style);

        cell3 = row1.createCell(7);
        // 单元格设值
        cell3.setCellValue("网卡检查");
        cell3.setCellStyle(style);

        cell4 = row2.createCell(7);
        // 单元格设值
        cell4.setCellValue("网卡状态");
        //设置单元格样式
        cell4.setCellStyle(style);



        if (count>0){
            DecimalFormat df = new DecimalFormat("#.##");   //格式化数字格式

            HSSFRow rows = sheet.createRow(3);
            for (int i = 0,j = 0; i < resultList.size(); i++,j=j+3) {
                row = sheet.createRow(j+3);
                row1 = sheet.createRow(j+4);
                row2 = sheet.createRow(j+5);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,1,1));
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultList.get(i).get("hostname").toString());
                //设置单元格样式
                cell.setCellStyle(style);

                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue(df.format(Double.valueOf((((Map<String,Object>)resultList.get(i).get("item")).get("CPUUtilization").toString())))+"%");   //cpu利用率
                //设置单元格样式
                cell.setCellStyle(style);

                cell = row.createCell(3);
                cell.setCellValue(df.format(Double.valueOf((((Map<String,Object>)resultList.get(i).get("item")).get("MemoryUtilization").toString())))+"%");    //内存利用率
                cell.setCellStyle(style);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,2,2));
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,3,3));

                cell = row.createCell(4);
                // 单元格设值
                cell.setCellValue("正常□\n异常□");
                //设置单元格样式
                cell.setCellStyle(cs);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,4,4));


                cell = row.createCell(5);
                // 单元格设值
                cell.setCellValue("正常□\n异常□");
                //设置单元格样式
                cell.setCellStyle(cs);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,5,5));


                cell1 = row.createCell(6);
                cell2 = row1.createCell(6);
                cell3 = row2.createCell(6);
                // 单元格设值
                cell1.setCellValue("指示灯:正常□ 异常□");
                cell2.setCellValue("电源:正常□ 异常□");
                cell3.setCellValue("风扇:正常□ 异常□");
                //设置单元格样式
                cell1.setCellStyle(cs1);
                cell2.setCellStyle(cs1);
                cell3.setCellStyle(cs1);


                cell = row.createCell(7);
                // 单元格设值
                cell.setCellValue("正常□\n异常□");
                //设置单元格样式
                cell.setCellStyle(cs);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j+3,j+5,7,7));

            }
            HSSFCell cells = rows.createCell(0);
            // 单元格设值
            if(hostType==0)
                cells.setCellValue("服务器");
            else
                cells.setCellValue("网络设备");
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(3,3*count+2,0,0));
            //设置单元格样式
            cells.setCellStyle(style);

            row = sheet.createRow(3*count+3);
            row.setHeight((short) 450);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("巡检人员");
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(5);
            cell.setCellValue("巡检日期");
            //设置单元格样式
            cell.setCellStyle(style);


            SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cell = row.createCell(6);
            // 单元格设值
            cell.setCellValue(df1.format(new Date()));
            //设置单元格样式
            cell.setCellStyle(cs1);

            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("早  □");
            //设置单元格样式
            cell.setCellStyle(cs1);

            row  = sheet.createRow(3*count+4);
            row.setHeight((short) 450);
            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("中  □");
            //设置单元格样式
            cell.setCellStyle(cs1);


            row  = sheet.createRow(3*count+5);
            row.setHeight((short) 450);
            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("晚  □");
            //设置单元格样式
            cell.setCellStyle(cs1);

        }else {
            row = sheet.createRow(3*count+3);
            HSSFCell cells = row.createCell(0);
            if(hostType==0)
                cells.setCellValue("服务器");
            else
                cells.setCellValue("网络设备");
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(3*count+3,3*count+3,1,7));
            cells = row.createCell(1);
            cells.setCellValue("无可用设备");
            cells.setCellStyle(style);


            row = sheet.createRow(3*count+4);
            row.setHeight((short) 450);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("巡检人员");
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(5);
            // 单元格设值
            cell.setCellValue("巡检日期");
            //设置单元格样式
            cell.setCellStyle(style);


            SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cell = row.createCell(6);
            cell.setCellValue(df1.format(new Date()));
            //设置单元格样式
            cell.setCellStyle(cs1);

            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("早  □");
            //设置单元格样式
            cell.setCellStyle(cs1);

            row  = sheet.createRow(3*count+5);
            row.setHeight((short) 450);
            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("中  □");
            //设置单元格样式
            cell.setCellStyle(cs1);


            row  = sheet.createRow(3*count+6);
            row.setHeight((short) 450);
            cell = row.createCell(7);
            // 单元格设值
            cell.setCellValue("晚  □");
            //设置单元格样式
            cell.setCellStyle(cs1);

        }











//        response.reset();
//        response.addHeader("Access-Control-Allow-Origin", "*");
//        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        // 设定保存类型 excel
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 保存文件的名称
        String fileName = "巡检报告";
        try {
            // 保存文件编码格式
            String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
            // 保存文件头部默认数据
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            // 字节流保存
            OutputStream os = response.getOutputStream();
            // 写入
            wb.write(os);
            // 刷新缓冲区
            os.flush();
            // 关闭字节流
            os.close();
        }catch (Exception e){
            e.getMessage();
            return false;
        }
        return true;
    }

    /**
     * 获得全部的告警问题  即acknowledge true和false同时输出
     * @param token
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllProblems(String token,String startDate,String endDate) {
        String severities = "";
        String hostIds = "";
        //
        // 定义输出
        final String[] outputs = new String[]{
                "object", "objectid", "severity", "acknowledged", "clock", "name", "source"
        };
        final String[] selectHostsParams = new String[]{"host"};
        Map<String, Object> paramsMap = new HashMap<String, Object>() {{
            put("selectHosts", selectHostsParams);
            put("output", outputs);
            put("severities", TextUtils.revertSeverity(severities));
            put("value", 1);
            // 开始时间
            put("time_from", DateUtils.timeToStamp(startDate)/1000);
            // 结束时间
            put("time_till", DateUtils.timeToStamp(endDate)/1000);
            // 根据clock排序
            put("sortfield", "clock");
            // 降序
            put("sortorder", "DESC");
        }};
        // 用，将主机列表切割
        if (!(hostIds == null || hostIds.isEmpty())) {
            String[] temp = hostIds.split(",");
            paramsMap.put("hostids", temp);
        }
        // 填入参数
        Map<String, Object> parameterMap = new HashMap<String, Object>() {{
            put("jsonrpc", "2.0");
            put("method", "event.get");
            put("id", 1);
            put("auth", token);
            put("params", paramsMap);
        }};
        // 结果序列化
        Map<String, Object> responseMap = APIUtils.sendRequest(JSON.toJSONString(parameterMap, SerializerFeature.WriteMapNullValue));
        List<Map<String, Object>> resultList = (List<Map<String, Object>>) responseMap.get("result");
        // 再次封装数据
        for (int i = resultList.size()-1; i >= 0; i--) {
            JSONArray jsonValues = (JSONArray) resultList.get(i).get("hosts");
            if (jsonValues.isEmpty()) {
                resultList.remove(i);
            } else {
                Map<String, Object> innerMap = jsonValues.getJSONObject(0);
                resultList.get(i).put("hostid", innerMap.get("hostid"));
                resultList.get(i).put("host", innerMap.get("host"));
            }
        }

        return resultList;
    }

    /**
     * 通过acknowledge过滤告警问题     true为已知晓(确定)的告警  false为未知晓(确定)的告警
     * @param token
     * @param startDate
     * @param endDate
     * @param acknowledged
     * @return
     */
    @Override
    public List<Map<String, Object>> getProblems(String token, String startDate, String endDate,Boolean acknowledged) {
        String severities = "";
        String hostIds = "";

        final String[] outputs = new String[]{
                "object", "objectid", "severity", "acknowledged", "clock", "name", "source"
        };
        final String[] selectHostsParams = new String[]{"host"};
        Map<String, Object> paramsMap = new HashMap<String, Object>() {{
            put("selectHosts", selectHostsParams);
            put("output", outputs);
            put("severities", TextUtils.revertSeverity(severities));
            put("value", 1);    // 1代表历史告警
            put("acknowledged", acknowledged);
            put("time_from", DateUtils.timeToStamp(startDate)/1000); // 开始时间
            put("time_till", DateUtils.timeToStamp(endDate)/1000); // 结束时间
            put("sortfield", "clock"); // 根据clock排序
            put("sortorder", "DESC"); // 降序
        }};
        // 用，将主机列表切割
        if (!(hostIds == null || hostIds.isEmpty())) {
            // 筛选主机
            String[] temp = hostIds.split(",");
            paramsMap.put("hostids", temp);
        }
        // 填入参数
        Map<String, Object> parameterMap = new HashMap<String, Object>() {{
            put("jsonrpc", "2.0");
            put("method", "event.get");
            put("id", 1);
            put("auth", token);
            put("params", paramsMap);
        }};
        // 结果序列化
        Map<String, Object> responseMap = APIUtils.sendRequest(JSON.toJSONString(parameterMap, SerializerFeature.WriteMapNullValue));
        List<Map<String, Object>> resultList = (List<Map<String, Object>>) responseMap.get("result");
        // 再次封装数据
        for (int i = resultList.size()-1; i >= 0; i--) {
            JSONArray jsonValues = (JSONArray) resultList.get(i).get("hosts");
            if (jsonValues.isEmpty()) {
                // 该事件的主机已被删除
                resultList.remove(i);
            } else {
                // 该事件的主机存在
                Map<String, Object> innerMap = jsonValues.getJSONObject(0);
                resultList.get(i).put("hostid", innerMap.get("hostid"));
                resultList.get(i).put("host", innerMap.get("host"));
            }
        }

        return resultList;
    }

    /**
     * 获得历史告警信息
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<Map<String,Object>> getHistoryAlertAndOrder(String startDate, String endDate) {
        final String token = APIUtils.getToken();
        ProblemServiceImpl problemService = new ProblemServiceImpl();
        Map<String, String> initHostItem = TextUtils.getTriggerDataToMap();     // 将获得的数据转化为Map
        List<Map<String, Object>> problemDataList = getAllProblems(token, startDate,endDate);   // 获得所有问题
        for (Map<String, Object> dataMap : problemDataList) {
            // 格式化严重等级
            TextUtils.transformSeverity(dataMap, "severity");
            // 格式化是否知晓
            TextUtils.transformAcknowledged(dataMap);
            // 格式化来源
            TextUtils.transformSource(dataMap);
            // 格式化问题出现时间
            DateUtils.formatListDate(dataMap, "clock");
            // 格式化内容
            String name = dataMap.get("name").toString();
            dataMap.put("name", initHostItem.get(name)==null?name:initHostItem.get(name));
            // 汉化name
            if (dataMap.get("name").toString().indexOf("Zabbix Server has been restarted")!=-1)
                dataMap.put("name","客户端已重新启动");
            else if (dataMap.get("name").toString().indexOf("has been restarted")!=-1)
                dataMap.put("name",dataMap.get("host")+" "+"已重新启动");
            else if (dataMap.get("name").toString().indexOf("The Memory Pages/sec is too high")!=-1)
                dataMap.put("name","内存页/秒太高");
            else {
                problemService.translateNameToChinese(dataMap);
                // 替换Zabbix字符串
                TextUtils.replaceString(dataMap, "Zabbix", "Client", "name");
            }
        }
        return problemDataList;

    }
    //告警分类统计(top10，告警分布统计+图，告警按日期统计，eg.周一周二周三...一月二月三月....)
    @Override
    public Boolean getHistoryAlertClassify(String startDate, String endDate,String imgsURl,HttpServletResponse response) {
        // 拿到API数据
        List<Map<String, Object>> problemDataList = getHistoryAlertAndOrder(startDate,endDate);     // 获得排序的历史数据
        List<Map<String, Object>> problemTop10 = new ArrayList<>();     //只取前十个

        // 根据host统计个数
        for (int i = 0; i < problemDataList.size(); i++) {
            int j = 0;
            for (; j < problemTop10.size(); j++) {
                if (problemDataList.get(i).get("host").toString().equals(problemTop10.get(j).get("host").toString())){
                    int count = (int) problemTop10.get(j).get("count")+1;
                    problemTop10.get(j).put("host",problemDataList.get(j).get("host"));
                    problemTop10.get(j).put("count",count);
                    break;
                }
            }
            if (j==problemTop10.size()) {
                int finalI = i;
                problemTop10.add(new HashMap<String, Object>(){{
                    put("host",problemDataList.get(finalI).get("host"));
                    put("count",1);
                }});
            }
        }
        // 排序
        Collections.sort(problemTop10, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Integer name1 = Integer.valueOf(o1.get("count").toString()) ;
                Integer name2 = Integer.valueOf(o2.get("count").toString()) ;
                return name2.compareTo(name1);
            }
        });
        // 只取<=个数据
        if (problemTop10.size()>10) {
            problemTop10 = problemTop10.subList(0,10);
        }
//        4. top10
//        System.out.println(problemTop10);

        /**取消日期统计
        int Monday = 0, Tuesday = 0, Wednesday = 0, Thursday = 0, Friday = 0, Saturday = 0, Sunday = 0;
        //此为一周/一月
        if ((DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==604800l || (DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==2592000l){
            for (int i = 0; i <problemDataList.size() ; i++) {
                switch (DateUtils.dateToWeek(problemDataList.get(i).get("clock").toString())){
                    case "星期一":
                        Monday++;
                        break;
                    case "星期二":
                        Tuesday++;
                        break;
                    case "星期三":
                        Wednesday++;
                        break;
                    case "星期四":
                        Thursday++;
                        break;
                    case "星期五":
                        Friday++;
                        break;
                    case "星期六":
                        Saturday++;
                        break;
                    case "星期日":
                        Sunday++;
                        break;
                    default:
                        break;
                    }
            }
        }
//        3. 按天统计告警数量
//        System.out.println(Monday+"  "+Tuesday+"  "+Wednesday+"  "+Thursday+"  "+Friday+"  "+Saturday+"  "+Sunday);
        int January = 0,February = 0,March = 0,April = 0, May = 0,June = 0,
                July = 0,August = 0,September = 0,October = 0,November = 0,December = 0;
        if ((DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==31536000l){
            for (int i = 0; i <problemDataList.size() ; i++) {
                switch (problemDataList.get(i).get("clock").toString().split("-")[1]){
                    case "01":
                        January++;
                        break;
                    case "02":
                        February++;
                        break;
                    case "03":
                        March++;
                        break;
                    case "04":
                        April++;
                        break;
                    case "05":
                        May++;
                        break;
                    case "06":
                        June++;
                        break;
                    case "07":
                        July++;
                        break;
                    case "08":
                        August++;
                        break;
                    case "09":
                        September++;
                        break;
                    case "10":
                        October++;
                        break;
                    case "11":
                        November++;
                        break;
                    case "12":
                        December++;
                        break;

                }
            }
        }
        **/
//        3. 按月统计告警数量
//        System.out.println(January+"  "+February+"  "+March+"  "+April+"  "+May+"  "+June+"  "+July+"  "+
//                August+"  "+September+"  "+October+"  "+November+"  "+December);



//        数据导出为excel
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String today = df.format(new Date());
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet  sheet = wb.createSheet();
        int index = 0;
        HSSFRow row = sheet.createRow(index);
        index++;
        HSSFCellStyle style = wb.createCellStyle();
        HSSFCellStyle style_temp = wb.createCellStyle();
        style_temp.setFillForegroundColor((short) 13);
        style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        sheet.setColumnWidth(0,6000);
        sheet.setColumnWidth(1,6000);
        sheet.setColumnWidth(2,10000);
        sheet.setColumnWidth(3,6000);
        sheet.setColumnWidth(4,6000);
        sheet.setColumnWidth(5,6000);
        sheet.setColumnWidth(6,6000);

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = row.createCell(0);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,2));
        // 单元格设值
        cell.setCellValue("时间:"+startDate.split(" ")[0]+startDate.split(" ")[1]+"至"+endDate.split(" ")[0]+endDate.split(" ")[1]);
        cell.setCellStyle(style);

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("设备分类告警Top10");
        //设置单元格样式
        cell.setCellStyle(style_temp);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,2));

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("序号");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("设备名称");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("告警数量");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        for (int i = 0; i < problemTop10.size(); i++) {
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue(i+1);
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(problemTop10.get(i).get("host").toString());
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(problemTop10.get(i).get("count").toString());
            //设置单元格样式
            cell.setCellStyle(style);
        }

        /** 取消日期统计导出
        if ((DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==604800l || (DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==2592000l){
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue("告警日期统计");
            cell.setCellStyle(style_temp);
            sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,6));

            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue("星期一");
            cell.setCellStyle(style_temp);

            cell = row.createCell(1);
            cell.setCellValue("星期二");
            cell.setCellStyle(style_temp);

            cell = row.createCell(2);
            cell.setCellValue("星期三");
            cell.setCellStyle(style_temp);

            cell = row.createCell(3);
            cell.setCellValue("星期四");
            cell.setCellStyle(style_temp);

            cell = row.createCell(4);
            cell.setCellValue("星期五");
            cell.setCellStyle(style_temp);

            cell = row.createCell(5);
            cell.setCellValue("星期六");
            cell.setCellStyle(style_temp);

            cell = row.createCell(6);
            cell.setCellValue("星期日");
            cell.setCellStyle(style_temp);

            //设置
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue(Monday);
            cell.setCellStyle(style);

            cell = row.createCell(1);
            cell.setCellValue(Tuesday);
            cell.setCellStyle(style);

            cell = row.createCell(2);
            cell.setCellValue(Wednesday);
            cell.setCellStyle(style);

            cell = row.createCell(3);
            cell.setCellValue(Thursday);
            cell.setCellStyle(style);

            cell = row.createCell(4);
            cell.setCellValue(Friday);
            cell.setCellStyle(style);

            cell = row.createCell(5);
            cell.setCellValue(Saturday);
            cell.setCellStyle(style);

            cell = row.createCell(6);
            cell.setCellValue(Sunday);
            cell.setCellStyle(style);
        }

        if ((DateUtils.timeToStamp(endDate)-DateUtils.timeToStamp(startDate))/1000==31536000l){
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue("告警日期统计");
            cell.setCellStyle(style_temp);
            sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,11));

            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue("一月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(1);
            cell.setCellValue("二月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(2);
            cell.setCellValue("三月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(3);
            cell.setCellValue("四月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(4);
            cell.setCellValue("五月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(5);
            cell.setCellValue("六月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(6);
            cell.setCellValue("七月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(7);
            cell.setCellValue("八月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(8);
            cell.setCellValue("九月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(9);
            cell.setCellValue("十月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(10);
            cell.setCellValue("十一月");
            cell.setCellStyle(style_temp);

            cell = row.createCell(11);
            cell.setCellValue("十二月");
            cell.setCellStyle(style_temp);

            //设值
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue(January);
            cell.setCellStyle(style);

            cell = row.createCell(1);
            cell.setCellValue(February);
            cell.setCellStyle(style);

            cell = row.createCell(2);
            cell.setCellValue(March);
            cell.setCellStyle(style);

            cell = row.createCell(3);
            cell.setCellValue(April);
            cell.setCellStyle(style);

            cell = row.createCell(4);
            cell.setCellValue(May);
            cell.setCellStyle(style);

            cell = row.createCell(5);
            cell.setCellValue(June);
            cell.setCellStyle(style);

            cell = row.createCell(6);
            cell.setCellValue(July);
            cell.setCellStyle(style);

            cell = row.createCell(7);
            cell.setCellValue(August);
            cell.setCellStyle(style);

            cell = row.createCell(8);
            cell.setCellValue(September);
            cell.setCellStyle(style);

            cell = row.createCell(9);
            cell.setCellValue(October);
            cell.setCellStyle(style);

            cell = row.createCell(10);
            cell.setCellValue(November);
            cell.setCellStyle(style);

            cell = row.createCell(11);
            cell.setCellValue(December);
            cell.setCellStyle(style);

        }
        **/
        List<Map<String,Object>> result = getAlarmDistributionAndChart(startDate,endDate);
        row = sheet.createRow(index);
        index++;

        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("");
        cell.setCellStyle(style_temp);
        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("告警级别分布统计");
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("时间:"+startDate.split(" ")[0]+"至"+endDate.split(" ")[0]);
        cell.setCellStyle(style_temp);
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,2,3));

        row = sheet.createRow(index);
        index++;

        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("");
        cell.setCellStyle(style_temp);
        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("未处理告警");
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("历史告警");
        cell.setCellStyle(style_temp);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue("总和");
        cell.setCellStyle(style_temp);

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("未分类");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue(result.get(0).get("weifenlei").toString());
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("weifenlei").toString());
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue(result.get(2).get("weifenlei").toString());
        cell.setCellStyle(style);

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("信息");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(result.get(0).get("xinxi").toString());
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("xinxi").toString());
        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue(result.get(2).get("xinxi").toString());
        cell.setCellStyle(style);


        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("警告");
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue(result.get(0).get("jinggao").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("jinggao").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue(result.get(2).get("jinggao").toString());
        //设置单元格样式
        cell.setCellStyle(style);



        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("一般警告");
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue(result.get(0).get("yibanyanzhong").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("yibanyanzhong").toString());
        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue(result.get(2).get("yibanyanzhong").toString());
        //设置单元格样式
        cell.setCellStyle(style);


        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("严重");
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue(result.get(0).get("yanzhong").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("yanzhong").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue(result.get(2).get("yanzhong").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("灾难");
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue(result.get(0).get("zainan").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue(result.get(1).get("zainan").toString());
        //设置单元格样式
        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue(result.get(2).get("zainan").toString());
        //设置单元格样式
        cell.setCellStyle(style);

//        response.reset();
//        response.addHeader("Access-Control-Allow-Origin", "*");
//        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        // 设定保存类型 excel
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 保存文件的名称
        String fileName = "告警统计报表";
//        String imgsURl = "";
        try {
            // 通过，将url切割
            String[] url = imgsURl.split(",");
            String u = url[1];
            // 采用buff将图片写入excel
            byte[] buffer = new BASE64Decoder().decodeBuffer(u);
//            OutputStream out = new FileOutputStream(new File("./src/main/resources/images/test.jpg"));
//            out.write(buffer);
//            out.flush();
//            out.close();
            row = sheet.createRow(index+3);
            HSSFCell headerCell = row.createCell(0);
            headerCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
            row = sheet.createRow(index + 6);
            HSSFCell cells = row.createCell(0);
            cells.setCellType(HSSFCell.CELL_TYPE_BLANK);
            ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
            ByteArrayInputStream inputStream = new ByteArrayInputStream(buffer);
//            BufferedImage bufferImg = ImageIO.read(new File("./src/main/resources/images/test.jpg"));
            BufferedImage bufferImg = ImageIO.read(inputStream);
            ImageIO.write(bufferImg, "PNG", outStream); // 利用HSSFPatriarch将图片写入EXCEL
            HSSFPatriarch patri = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor((short) 0, (short) 0, (short) 0, (short) 0, (short) 0, (short) index, (short) 5, (short) (index+45));
            patri.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // 保存文件编码格式
            String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
            // 保存文件头部默认数据
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            // 字节流保存
            OutputStream os = response.getOutputStream();
            // 写入
            wb.write(os);
            // 刷新缓冲区
            os.flush();
            // 关闭字节流
            os.close();
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

    //告警统计(按日期导出全部)
    @Override
    public Boolean getHistoryAlert(String startDate, String endDate,HttpServletResponse response) {
        String dateStr = DateUtils.timeToStamp(startDate)+","+DateUtils.timeToStamp(endDate);
//        List<Map<String, Object>> problemDataList = getHistoryAlertAndOrder(startDate,endDate);
        final String token = APIUtils.getToken();
        List<Map<String, Object>> problemDataList1 = problemService.getProblemsAndAcknowledgedProblems(token,"", "", dateStr,1);
//        List<Map<String, Object>> problemDataList = problemService.getProblems(token, true, "", "", dateStr);
        List<Map<String, Object>> problemDataList = problemService.getProblems_new(true, "", "", dateStr);
        for (int i=0;i<problemDataList1.size();i++)
            problemDataList.add(problemDataList1.get(i));
        Collections.sort(problemDataList, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Long name1 = Long.valueOf(o1.get("clock").toString()) ;
                Long name2 = Long.valueOf(o2.get("clock").toString()) ;
                return name2.compareTo(name1);
            }
        });
        if (problemDataList.size()>1000)
            problemDataList = problemDataList.subList(0,1000);
//        数据导出为excel
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet  sheet = wb.createSheet();
        HSSFRow row ;
        int index = 0;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");       //格式化时间
        String today = df.format(new Date());

        HSSFCellStyle style = wb.createCellStyle();
        HSSFCellStyle style_temp = wb.createCellStyle();
        style_temp.setFillForegroundColor((short) 13);
        style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        sheet.setColumnWidth(0,6000);
        sheet.setColumnWidth(1,6000);
        sheet.setColumnWidth(2,10000);
        sheet.setColumnWidth(3,6000);
        sheet.setColumnWidth(4,6000);

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        row = sheet.createRow(index);
        index++;
        HSSFCell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,4));
        // 单元格设值
        cell.setCellValue("时间： "+today);
        cell.setCellStyle(style);

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("历史告警");
        //设置单元格样式
        cell.setCellStyle(style_temp);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,4));
        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("序号");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("设备名称");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("内容");
        //设置单元格样式
        cell.setCellStyle(style_temp);

//        cell = row.createCell(3);
//        cell.setCellValue("状态");
//        cell.setCellStyle(style);

        cell = row.createCell(3);
        // 单元格设值
        cell.setCellValue("严重性");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(4);
        // 单元格设值
        cell.setCellValue("创建时间");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        //加入状态
//        for (int i = 0; i < problemDataList.size(); i++) {
//            List<Map<String, Object>> DataList = problemService.getProblemByObjectID(problemDataList.get(i).get("objectid").toString());
//            if (!DataList.isEmpty())
//                problemDataList.get(i).put("status","问题");
//            else
//                problemDataList.get(i).put("status","已解决");
//        }
        String[] severities = {"未分类","信息类","警告","一般严重","严重","灾难"};
        for (int i = 0; i < problemDataList.size(); i++) {
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            cell.setCellValue(i+1);
            cell.setCellStyle(style);

            cell = row.createCell(1);
            cell.setCellValue(problemDataList.get(i).get("host").toString());
            cell.setCellStyle(style);

            cell = row.createCell(2);
            String name = problemDataList.get(i).get("name").toString();
            if (name.equals("Client agent is not available (for 3m)"))
                name = "客户端代理不可用";
            // 单元格设值
            cell.setCellValue(name);
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(severities[Integer.valueOf(problemDataList.get(i).get("severity").toString())]);
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(4);
            // 单元格设值
            cell.setCellValue(DateUtils.SecondTimeStamp2Date(problemDataList.get(i).get("clock").toString()));
            //设置单元格样式
            cell.setCellStyle(style);
        }
//        response.reset();
//        response.addHeader("Access-Control-Allow-Origin", "*");
//        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        // 设定保存类型 excel
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 保存文件的名称
        String fileName = "告警信息报表";
        try {
            // 保存文件编码格式
            String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
            // 保存文件头部默认数据
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            // 字节流保存
            OutputStream os = response.getOutputStream();
            // 写入
            wb.write(os);
            // 刷新缓冲区
            os.flush();
            // 关闭字节流
            os.close();
        } catch (Exception e) {
            e.getMessage();
            return false;
        }
        return true;
    }


    //获得告警分布 统计+图形
    @Override
    public List<Map<String,Object>> getAlarmDistributionAndChart(String startDate, String endDate) {
        final String token = APIUtils.getToken();
        // problemDataList1 为未处理数据,需要去重，将旧信息移除，旧信息属于历史告警问题
//        List<Map<String, Object>> problemDataList1 = getProblems(token,startDate,endDate,false);
        List<Map<String, Object>> problemDataList1 = getAllCurrentUnhandledProblems(token,startDate,endDate);
        // problemDataList = problemDataList2 + problemDataList1 为历史告警
//        List<Map<String, Object>> problemDataList2 = getAllProblems(token,startDate,endDate);
        String dateStr = DateUtils.timeToStamp(startDate)+","+DateUtils.timeToStamp(endDate);
        List<Map<String, Object>> problemDataList2 = problemService.getProblemsAndAcknowledgedProblems(token,"", "", dateStr,1);
//        List<Map<String, Object>> problemDataList = problemService.getProblems(token, true, "", "", dateStr);
        List<Map<String, Object>> problemDataList = problemService.getProblems_new( true, "", "", dateStr);
        for (int i=0;i<problemDataList2.size();i++)
            problemDataList.add(problemDataList2.get(i));
        // 根据clock排序
        Collections.sort(problemDataList, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Long name1 = Long.valueOf(o1.get("clock").toString()) ;
                Long name2 = Long.valueOf(o2.get("clock").toString()) ;
                return name2.compareTo(name1);
            }
        });

        // 筛选未处理告警，即除时间外其他数据一样的数据
        for (int i = 0; i < problemDataList1.size(); i++) {
            int j = i+1;
            for (; j < problemDataList1.size(); j++) {
                if (problemDataList1.get(i).get("host").toString().equals(problemDataList1.get(j).get("host").toString()) &&
                        problemDataList1.get(i).get("name").toString().equals(problemDataList1.get(j).get("name").toString()) &&
                        !(problemDataList1.get(i).get("clock").toString().equals(problemDataList1.get(j).get("clock").toString()))
                )
                    break;
                if (problemDataList1.get(i).get("host").toString().equals(problemDataList1.get(j).get("host").toString()) &&
                        problemDataList1.get(i).get("name").toString().equals(problemDataList1.get(j).get("name").toString()) &&
                        problemDataList1.get(i).get("clock").toString().equals(problemDataList1.get(j).get("clock").toString())
                )
                    break;
            }
            if (j!=problemDataList1.size()){
                problemDataList1.remove(j);
                i--;
            }
        }
        //未处理数量
        int weifenlei1 = 0,xinxi1 = 0,jinggao1 = 0,yibanyanzhong1 = 0,yanzhong1 = 0,zainan1 = 0;
        //历史数量
        int weifenlei2 = 0,xinxi2 = 0,jinggao2 = 0,yibanyanzhong2 = 0,yanzhong2 = 0,zainan2 = 0;
        //综合
        int weifenlei = 0,xinxi = 0,jinggao = 0,yibanyanzhong = 0,yanzhong = 0,zainan = 0;
        for (int i = 0; i < problemDataList1.size(); i++) {
            switch (problemDataList1.get(i).get("severity").toString()){
                case "0":
                    weifenlei1++;
                    break;
                case "1":
                    xinxi1++;
                    break;
                case "2":
                    jinggao1++;
                    break;
                case "3":
                    yibanyanzhong1++;
                    break;
                case "4":
                    yanzhong1++;
                    break;
                case "5":
                    zainan1++;
                    break;
                default:
                    break;
            }
        }
        for (int i = 0; i < problemDataList.size(); i++) {
            switch (problemDataList.get(i).get("severity").toString()){
                case "0":
                    weifenlei2++;
                    break;
                case "1":
                    xinxi2++;
                    break;
                case "2":
                    jinggao2++;
                    break;
                case "3":
                    yibanyanzhong2++;
                    break;
                case "4":
                    yanzhong2++;
                    break;
                case "5":
                    zainan2++;
                    break;
                default:
                    break;
            }
        }
        // 计算未处理/历史告警的 告警分类统计
        weifenlei = weifenlei1 + weifenlei2;
        xinxi = xinxi1 + xinxi2;
        jinggao = jinggao1 + jinggao2;
        yibanyanzhong = yibanyanzhong1 + yibanyanzhong2;
        yanzhong = yanzhong1 + yanzhong2;
        zainan = zainan1 + zainan2;
        Map<String,Object> resultMap1 = new HashMap<>();
        Map<String,Object> resultMap2 = new HashMap<>();
        Map<String,Object> resultMap3 = new HashMap<>();
        List<Map<String,Object>> resultMap = new ArrayList<>();
        //封告警级别装数据
        resultMap1.put("weifenlei",weifenlei1);
        resultMap1.put("xinxi",xinxi1);
        resultMap1.put("jinggao",jinggao1);
        resultMap1.put("yibanyanzhong",yibanyanzhong1);
        resultMap1.put("yanzhong",yanzhong1);
        resultMap1.put("zainan",zainan1);

        resultMap2.put("weifenlei",weifenlei2);
        resultMap2.put("xinxi",xinxi2);
        resultMap2.put("jinggao",jinggao2);
        resultMap2.put("yibanyanzhong",yibanyanzhong2);
        resultMap2.put("yanzhong",yanzhong2);
        resultMap2.put("zainan",zainan2);

        resultMap3.put("weifenlei",weifenlei);
        resultMap3.put("xinxi",xinxi);
        resultMap3.put("jinggao",jinggao);
        resultMap3.put("yibanyanzhong",yibanyanzhong);
        resultMap3.put("yanzhong",yanzhong);
        resultMap3.put("zainan",zainan);

        resultMap.add(resultMap1);
        resultMap.add(resultMap2);
        resultMap.add(resultMap3);
        return resultMap;
    }

    /**
     * 获得告警分布饼图数据
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<List> getAlarmDistributionAndChartData(String startDate, String endDate) {
//        ProblemServiceImpl problemService = new ProblemServiceImpl();
        final String token = APIUtils.getToken();
        // problemDataList1 为未处理数据,需要去重，将旧信息移除，旧信息属于历史告警问题
//        List<Map<String, Object>> problemDataList1 = getProblems(token,startDate,endDate,false);
        List<Map<String, Object>> problemDataList1 = getAllCurrentUnhandledProblems(token,startDate,endDate);
        // problemDataList = problemDataList2 + problemDataList1 为历史告警
//        List<Map<String, Object>> problemDataList2 = getHistoryAlertAndOrder(startDate,endDate);
//        List<Map<String, Object>> probelemDataList2 = getAllProblems(token,startDate,endDate);
        String dateStr = DateUtils.timeToStamp(startDate)+","+DateUtils.timeToStamp(endDate);
//        String dateStr = "1655886322996,1656491122996";
        // 拿到API数据  获取到状态未确认但是问题已经关闭或者没有关闭的旧消息
        List<Map<String, Object>> problemDataList2 = problemService.getProblemsAndAcknowledgedProblems(token,"", "", dateStr,1);
//        List<Map<String, Object>> problemDataList = problemService.getProblems(token, true, "", "", dateStr);
        List<Map<String, Object>> problemDataList = problemService.getProblems_new( true, "", "", dateStr);


        // 拿到API数据 //获取已经确认过的消息

        for (int i=0;i<problemDataList2.size();i++)
            problemDataList.add(problemDataList2.get(i));
        Collections.sort(problemDataList, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Long name1 = Long.valueOf(o1.get("clock").toString()) ;
                Long name2 = Long.valueOf(o2.get("clock").toString()) ;
                return name2.compareTo(name1);
            }
        });
        if (problemDataList.size()>1000)
            problemDataList = problemDataList.subList(0,1000);


//        List<Map<String,Object>> problemDataList = problemDataList2;
//        for (int i = 0; i < problemDataList1.size(); i++) {
//            problemDataList.add(problemDataList1.get(i));
//        }
//        Collections.sort(problemDataList, new Comparator<Map<String, Object>>() {
//            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
//                Long name1 = Long.valueOf(o1.get("clock").toString()) ;
//                Long name2 = Long.valueOf(o2.get("clock").toString()) ;
//                return name2.compareTo(name1);
//            }
//        });
        // 筛选未处理告警，即除时间外其他数据一样的数据
        for (int i = 0; i < problemDataList1.size(); i++) {
            int j = i+1;
            for (; j < problemDataList1.size(); j++) {
                if (problemDataList1.get(i).get("host").toString().equals(problemDataList1.get(j).get("host").toString()) &&
                        problemDataList1.get(i).get("name").toString().equals(problemDataList1.get(j).get("name").toString()) &&
                        !(problemDataList1.get(i).get("clock").toString().equals(problemDataList1.get(j).get("clock").toString()))
                )
                    break;
                if (problemDataList1.get(i).get("host").toString().equals(problemDataList1.get(j).get("host").toString()) &&
                        problemDataList1.get(i).get("name").toString().equals(problemDataList1.get(j).get("name").toString()) &&
                        problemDataList1.get(i).get("clock").toString().equals(problemDataList1.get(j).get("clock").toString())
                )
                    break;
            }
            if (j!=problemDataList1.size()){
                problemDataList1.remove(j);
                i--;
            }
        }

        //未处理数量
        int weifenlei1 = 0,xinxi1 = 0,jinggao1 = 0,yibanyanzhong1 = 0,yanzhong1 = 0,zainan1 = 0;
        //历史数量
        int weifenlei2 = 0,xinxi2 = 0,jinggao2 = 0,yibanyanzhong2 = 0,yanzhong2 = 0,zainan2 = 0;
        //综合
        int weifenlei = 0,xinxi = 0,jinggao = 0,yibanyanzhong = 0,yanzhong = 0,zainan = 0;
        for (int i = 0; i < problemDataList1.size(); i++) {
            switch (problemDataList1.get(i).get("severity").toString()){
                case "0":
                    weifenlei1++;
                    break;
                case "1":
                    xinxi1++;
                    break;
                case "2":
                    jinggao1++;
                    break;
                case "3":
                    yibanyanzhong1++;
                    break;
                case "4":
                    yanzhong1++;
                    break;
                case "5":
                    zainan1++;
                    break;
                default:
                    break;
            }
        }
        for (int i = 0; i < problemDataList.size(); i++) {
            switch (problemDataList.get(i).get("severity").toString()){
                case "0":
                    weifenlei2++;
                    break;
                case "1":
                    xinxi2++;
                    break;
                case "2":
                    jinggao2++;
                    break;
                case "3":
                    yibanyanzhong2++;
                    break;
                case "4":
                    yanzhong2++;
                    break;
                case "5":
                    zainan2++;
                    break;
                default:
                    break;
            }
        }
        // 计算未处理/历史告警的 告警分类统计
        weifenlei = weifenlei1 + weifenlei2;
        xinxi = xinxi1 + xinxi2;
        jinggao = jinggao1 + jinggao2;
        yibanyanzhong = yibanyanzhong1 + yibanyanzhong2;
        yanzhong = yanzhong1 + yanzhong2;
        zainan = zainan1 + zainan2;
        ArrayList<Object> list1 = new ArrayList<>();
        ArrayList<Object> list2 = new ArrayList<>();
        ArrayList<Object> list3 = new ArrayList<>();
        List<List> resultMap = new ArrayList<>();
        List<Map<String,Object>> resultTemp = new ArrayList<>();
        Map<String,Object> weichuli = new HashMap<String, Object>(){{
            put("name","未处理告警");
        }};
        weichuli.put("weifenlei",weifenlei1);
        weichuli.put("xinxi",xinxi1);
        weichuli.put("jinggao",jinggao1);
        weichuli.put("yibanyanzhong",yibanyanzhong1);
        weichuli.put("yanzhong",yanzhong1);
        weichuli.put("zainan",zainan1);
        resultTemp.add(weichuli);

        list1.add(weifenlei1);
        list1.add(xinxi1);
        list1.add(jinggao1);
        list1.add(yibanyanzhong1);
        list1.add(yanzhong1);
        list1.add(zainan1);

        Map<String,Object> lishi = new HashMap<String, Object>(){{
            put("name","历史告警");
        }};
        lishi.put("weifenlei",weifenlei2);
        lishi.put("xinxi",xinxi2);
        lishi.put("jinggao",jinggao2);
        lishi.put("yibanyanzhong",yibanyanzhong2);
        lishi.put("yanzhong",yanzhong2);
        lishi.put("zainan",zainan2);
        resultTemp.add(lishi);

        list2.add(weifenlei2);
        list2.add(xinxi2);
        list2.add(jinggao2);
        list2.add(yibanyanzhong2);
        list2.add(yanzhong2);
        list2.add(zainan2);
        Map<String,Object> zonghe = new HashMap<String, Object>(){{
            put("name","总和");
        }};
        zonghe.put("weifenlei",weifenlei);
        zonghe.put("xinxi",xinxi);
        zonghe.put("jinggao",jinggao);
        zonghe.put("yibanyanzhong",yibanyanzhong);
        zonghe.put("yanzhong",yanzhong);
        zonghe.put("zainan",zainan);
        resultTemp.add(zonghe);

        list3.add(weifenlei);
        list3.add(xinxi);
        list3.add(jinggao);
        list3.add(yibanyanzhong);
        list3.add(yanzhong);
        list3.add(zainan);

        resultMap.add(list1);
        resultMap.add(list2);
        resultMap.add(list3);
        resultMap.add(resultTemp);
        return resultMap;
    }


    //获得资源报表信息,无参，导出全部
    @Override
    public void getResource(HttpServletResponse response) {
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        PortServiceImpl portService = new PortServiceImpl();
        final Integer count1 = monitorService.getHostCountByTypeAndStatus(0,"可用");  // 服务器获得可用设备的数量
        List<Map<String,Object>> resultList1 = monitorService.getHostListAvailClassify(1,count1,"可用",0);    //获得服务器可用设备列表
        final Integer count2 = monitorService.getHostCountByTypeAndStatus(1,"可用");  // 网络设备获得可用设备的数量
        List<Map<String,Object>> resultList2 = monitorService.getHostListAvailClassify(1,count2,"可用",1);        //获得网络设备可用设备列表
        // 注释同最上面的方法注释
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        HSSFRow row;
        int index = 0;
        HSSFCellStyle style = wb.createCellStyle();
        HSSFCellStyle style_temp = wb.createCellStyle();
        style_temp.setFillForegroundColor((short) 13);
        style_temp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_temp.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        sheet.setColumnWidth(0,8000);
        sheet.setColumnWidth(1,8000);
        sheet.setColumnWidth(2,8000);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        row = sheet.createRow(index);
        index++;
        HSSFCell cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("服务器资源");
        cell.setCellStyle(style_temp);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,2));

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("设备名称");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("IP");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("操作系统");
        //设置单元格样式
        cell.setCellStyle(style_temp);
        for (int i = 0; i < resultList1.size(); i++) {
            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue(resultList1.get(i).get("hostname").toString());
            cell.setCellStyle(style);

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(resultList1.get(i).get("ip").toString());
            //设置单元格样式
            cell.setCellStyle(style);


            Map<String,Object> map = (Map<String, Object>) resultList1.get(i).get("item");
            if (map.get("DiskWriterateWindows").toString()!=""){
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("Windows");
                //设置单元格样式
                cell.setCellStyle(style);
            }
            else{
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("Linux");
                //设置单元格样式
                cell.setCellStyle(style);
            }
        }

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("网络设备资源");
        //设置单元格样式
        cell.setCellStyle(style_temp);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(index-1,index-1,0,2));

        row = sheet.createRow(index);
        index++;
        cell = row.createCell(0);
        // 单元格设值
        cell.setCellValue("设备名称");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(1);
        // 单元格设值
        cell.setCellValue("设备型号");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        cell = row.createCell(2);
        // 单元格设值
        cell.setCellValue("网口数量");
        //设置单元格样式
        cell.setCellStyle(style_temp);

        for (int i = 0; i < resultList2.size(); i++) {
            List<Map<String,Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList2.get(i).get("hostid").toString());  //通过设备hostid获得接口信息
            Map<String,String> info = portService.getInfomtionByhostId(resultList2.get(i).get("hostid").toString());

            row = sheet.createRow(index);
            index++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue(resultList1.get(i).get("hostname").toString());
            //设置单元格样式
            cell.setCellStyle(style);

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(info.get("Hardware_model_name"));
            cell.setCellStyle(style);

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(dataMap.size());
            //设置单元格样式
            cell.setCellStyle(style);

        }
//        response.reset();
//        response.addHeader("Access-Control-Allow-Origin", "*");
//        response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
        // 设定保存类型 excel
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 保存文件的名称
        String fileName = "资源统计报表";
        try {
            // 保存文件编码格式
            String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
            // 保存文件头部默认数据
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            // 字节流保存
            OutputStream os = response.getOutputStream();
            // 写入
            wb.write(os);
            // 刷新缓冲区
            os.flush();
            // 关闭字节流
            os.close();
        }catch (Exception e){
            e.getMessage();
        }

    }

    /**
     *新  获得资源报表信息,无参，导出全部
     * @param hostType
     * @param response
     * @return
     */
    @Override
    public Boolean getResource_new(String hostType, HttpServletResponse response) {
        if ("0".equals(hostType)){
            // 资源 设备统计 服务器/交换机/单项网闸/防火墙
            int i = 0;
            int AvailSwitch=0,UnAvailSwitch=0;
            int AvailSingleGatekeeper=0,UnAvailSingleGatekeeper=0;
            int AvailFirewall = 0,UnAvailFirewall = 0;
            MonitorServiceImpl monitorService = new MonitorServiceImpl();
            PortServiceImpl portService = new PortServiceImpl();
            // 获得可用和不可用的设备和数量
            final Integer count1 = monitorService.getHostCountByTypeAndStatus(0,"可用");
            List<Map<String,Object>> resultList1 = monitorService.getHostListAvailClassify(1,count1,"可用",0);
            final Integer count2 = monitorService.getHostCountByTypeAndStatus(0,"不可用");
            List<Map<String,Object>> resultList2 = monitorService.getHostListAvailClassify(1,count2,"不可用",0);
            final Integer count3 = monitorService.getHostCountByTypeAndStatus(1,"可用");
            List<Map<String,Object>> resultList3 = monitorService.getHostListAvailClassify(1,count3,"可用",1);
            final Integer count4 = monitorService.getHostCountByTypeAndStatus(1,"不可用");
            List<Map<String,Object>> resultList4 = monitorService.getHostListAvailClassify(1,count4,"不可用",1);
            for (int j = 0; j < resultList3.size(); j++) {
                Map<String,Object> map = (Map<String, Object>) resultList3.get(j).get("inventory");
                if ("firewall".equals(map.get("notes")))
                    AvailFirewall++;
                else if("single_gate".equals(map.get("notes")))
                    AvailSingleGatekeeper++;
                else
                    AvailSwitch++;
            }
            for (int j = 0; j < resultList4.size(); j++) {
                Map<String,Object> map = (Map<String, Object>) resultList4.get(j).get("inventory");
                if ("firewall".equals(map.get("notes")))
                    UnAvailFirewall++;
                else if ("single_gate".equals(map.get("notes")))
                    UnAvailSingleGatekeeper++;
                else
                    UnAvailSwitch++;
            }
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFSheet sheet = wb.createSheet("按设备类型统计");
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String today = df.format(new Date());
            HSSFRow row = sheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 1));
            i++;
            sheet.setColumnWidth(0,6000);
            sheet.setColumnWidth(1,6000);
            sheet.setColumnWidth(2,6000);
            sheet.setColumnWidth(3,6000);
            // 单元格设值
            cell.setCellValue("时间： "+today);
            //设置单元格样式
            cell.setCellStyle(style);

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("设备分类名称");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("资源总数");
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("可用数量");
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("不可用数量");

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("服务器");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(count1+count2);
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(count1);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(count2);

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("交换机");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(AvailSwitch+UnAvailSwitch);
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(AvailSwitch);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(UnAvailSwitch);

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("单项网闸");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(AvailSingleGatekeeper+UnAvailSingleGatekeeper);
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(AvailSingleGatekeeper);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(UnAvailSingleGatekeeper);


            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("防火墙");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue(AvailFirewall+UnAvailFirewall);
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue(AvailFirewall);
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue(UnAvailFirewall);


            i = 0;
            sheet = wb.createSheet("服务器");
            row = sheet.createRow(i);
            cell = row.createCell(0);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            sheet.setColumnWidth(0,6000);
            sheet.setColumnWidth(1,6000);
            sheet.setColumnWidth(2,6000);
            sheet.setColumnWidth(3,6000);
            // 单元格设值
            cell.setCellValue("时间： "+today);
            //设置单元格样式
            cell.setCellStyle(style);


            row = sheet.createRow(i);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("服务器资源");
            cell.setCellStyle(style);

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("设备名称");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("IP");
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("操作系统");
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("状态");

            for (int j = 0; j < resultList1.size(); j++) {
                row = sheet.createRow(i);
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue(resultList1.get(j).get("hostname").toString());
                cell.setCellStyle(style);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultList1.get(j).get("ip").toString());
                //设置单元格样式
                cell.setCellStyle(style);


                Map<String,Object> map = (Map<String, Object>) resultList1.get(j).get("item");
                if (map.get("DiskWriterateWindows").toString()!=""){
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("Windows");
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
                else{
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("Linux");
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
                // 单元格设值
                cell = row.createCell(3);
                cell.setCellValue("可用");
            }

            for (int j = 0; j < resultList2.size(); j++) {
                row = sheet.createRow(i);
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue(resultList2.get(j).get("hostname").toString());
                //设置单元格样式
                cell.setCellStyle(style);

                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultList2.get(j).get("ip").toString());
                //设置单元格样式
                cell.setCellStyle(style);


                Map<String,Object> map = (Map<String, Object>) resultList2.get(j).get("item");
                if (map.get("DiskWriterateWindows").toString()!=""){
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("Windows");
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
                else{
                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue("Linux");
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue("不可用");
            }


            i = 0;
            sheet = wb.createSheet("交换机");
            row = sheet.createRow(i);
            cell = row.createCell(0);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            sheet.setColumnWidth(0,6000);
            sheet.setColumnWidth(1,6000);
            sheet.setColumnWidth(2,6000);
            sheet.setColumnWidth(3,6000);
            // 单元格设值
            cell.setCellValue("时间： "+today);
            //设置单元格样式
            cell.setCellStyle(style);


            row = sheet.createRow(i);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("交换机资源");
            //设置单元格样式
            cell.setCellStyle(style);

            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("设备名称");

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("设备型号");

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("网口数量");

            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("状态");

            HSSFSheet sheet1 =wb.createSheet("单项网闸");
            int p = 0;
            row = sheet1.createRow(p);
            cell = row.createCell(0);
            //合并单元格
            sheet1.addMergedRegion(new CellRangeAddress(p, p, 0, 3));
            p++;
            sheet1.setColumnWidth(0,6000);
            sheet1.setColumnWidth(1,6000);
            sheet1.setColumnWidth(2,6000);
            sheet1.setColumnWidth(3,6000);
            // 单元格设值
            cell.setCellValue("时间： "+today);
            //设置单元格样式
            cell.setCellStyle(style);
            row = sheet1.createRow(p);
            //合并单元格
            sheet1.addMergedRegion(new CellRangeAddress(p, p, 0, 3));
            p++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("单项网闸资源");
            //设置单元格样式
            cell.setCellStyle(style);

            row = sheet1.createRow(p);
            p++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("设备名称");

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("设备型号");

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("网口数量");

            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("状态");

            HSSFSheet sheet2 =wb.createSheet("防火墙");
            int q = 0;
            row = sheet2.createRow(q);
            cell = row.createCell(0);
            //合并单元格
            sheet2.addMergedRegion(new CellRangeAddress(q, q, 0, 3));
            q++;
            sheet2.setColumnWidth(0,6000);
            sheet2.setColumnWidth(1,6000);
            sheet2.setColumnWidth(2,6000);
            sheet2.setColumnWidth(3,6000);
            cell.setCellValue("时间： "+today);
            cell.setCellStyle(style);
            row = sheet2.createRow(q);
            //合并单元格
            sheet2.addMergedRegion(new CellRangeAddress(q, q, 0, 3));
            q++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("防火墙资源");
            cell.setCellStyle(style);

            row = sheet2.createRow(q);
            q++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("设备名称");

            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("设备型号");

            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("网口数量");

            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("状态");
            String name;


            for (int j = 0; j < resultList3.size(); j++) {
                name = "无数据";
                Map<String,Object> map = (Map<String, Object>) resultList3.get(j).get("inventory");
                if (map.get("notes")=="single_gate")
                {
                    List<Map<String, Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList4.get(j).get("hostid").toString());
                    Map<String, String> info = portService.getInfomtionByhostId(resultList4.get(j).get("hostid").toString());

                    row = sheet1.createRow(p);
                    p++;
                    cell = row.createCell(0);
                    if (!"".equals(resultList4.get(j).get("hostname").toString()))
                        name = resultList4.get(j).get("hostname").toString();
                    // 单元格设值
                    cell.setCellValue(name);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(info.get("Hardware_model_name")); //键值可能要根据后面更改

                    cell = row.createCell(2);
                    cell.setCellValue(dataMap.size());

                    cell = row.createCell(3);
                    cell.setCellValue("可用");
                }
                else if ("firewall".equals(map.get("notes"))){
                    List<Map<String,Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList3.get(j).get("hostid").toString());      // 通过设备hostid获得接口信息
                    Map<String,String> info = portService.getInfomtionByhostId(resultList3.get(j).get("hostid").toString());        // 通过设备hostid获得交换机的序列号和温度

                    row = sheet2.createRow(q);
                    q++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList3.get(j).get("hostname").toString());

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(info.get("Hardware_model_name"));

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(dataMap.size());

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("可用");
                }
                else {
                    List<Map<String,Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList3.get(j).get("hostid").toString());
                    Map<String,String> info = portService.getInfomtionByhostId(resultList3.get(j).get("hostid").toString());

                    row = sheet.createRow(i);
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList3.get(j).get("hostname").toString());

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(info.get("Hardware_model_name"));

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(dataMap.size());

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("可用");
                }
            }

            for (int j = 0; j < resultList4.size(); j++) {
                name = "无数据";
                Map<String, Object> map = (Map<String, Object>) resultList4.get(j).get("inventory");
                if ("single_gate".equals(map.get("notes"))) {
                    row = sheet1.createRow(p);
                    p++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList4.get(j).get("hostname").toString());

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue("无数据");

                    cell = row.createCell(2);
                    cell.setCellValue(0);

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("不可用");
                }
                else if ("firewall".equals(map.get("notes"))){
                    List<Map<String, Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList4.get(j).get("hostid").toString());
                    Map<String, String> info = portService.getInfomtionByhostId(resultList4.get(j).get("hostid").toString());

                    row = sheet2.createRow(q);
                    q++;
                    cell = row.createCell(0);
                    if (!"".equals(resultList4.get(j).get("hostname").toString()))
                        name = resultList4.get(j).get("hostname").toString();
                    // 单元格设值
                    cell.setCellValue(name);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(info.get("Hardware_model_name"));

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(dataMap.size());

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("不可用");
                }
                else {
                    List<Map<String, Object>> dataMap = monitorService.getInterfaceOfSnmpByHostId(resultList4.get(j).get("hostid").toString());
                    Map<String, String> info = portService.getInfomtionByhostId(resultList4.get(j).get("hostid").toString());
                    row = sheet.createRow(i);
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(resultList4.get(j).get("hostname").toString());

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(info.get("Hardware_model_name"));

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(dataMap.size());

                    cell = row.createCell(3);
                    // 单元格设值
                    cell.setCellValue("不可用");
                }
            }




//            response.reset();
//            response.addHeader("Access-Control-Allow-Origin", "*");
//            response.addHeader("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE");
            // 设定保存类型 excel
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            // 保存文件的名称
            String fileName = "资源统计报表";
            try {
                // 保存文件编码格式
                String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
                // 保存文件头部默认数据
                response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                // 字节流保存
                OutputStream os = response.getOutputStream();
                // 写入
                wb.write(os);
                // 刷新缓冲区
                os.flush();
                // 关闭字节流
                os.close();
                return true;
            }catch (Exception e){
                e.getMessage();
                return false;
            }
        }else { // 业务类型统计
            int i = 0;
            List<Map<String,Object>> resultList = businessService.getBusinessReport();  // 获得所有的业务
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFSheet sheet = wb.createSheet("按业务类型统计");
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String today = df.format(new Date());
            HSSFRow row = sheet.createRow(i);
            HSSFCell cell = row.createCell(0);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 1));
            i++;
            sheet.setColumnWidth(0,6000);
            sheet.setColumnWidth(1,6000);
            sheet.setColumnWidth(2,6000);
            sheet.setColumnWidth(3,6000);
            // 单元格设值
            cell.setCellValue("时间： "+today);
            //设置单元格样式
            cell.setCellStyle(style);
            row = sheet.createRow(i);
            i++;
            cell = row.createCell(0);
            // 单元格设值
            cell.setCellValue("业务分类名称");
            cell = row.createCell(1);
            // 单元格设值
            cell.setCellValue("资源总数");
            cell = row.createCell(2);
            // 单元格设值
            cell.setCellValue("设备资源");
            cell = row.createCell(3);
            // 单元格设值
            cell.setCellValue("数据库资源");
            for (int j = 0; j < resultList.size(); j++) {
                row = sheet.createRow(i);
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("name").toString());
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("num").toString());
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("num1").toString());
                cell = row.createCell(3);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("num2").toString());
            }

            for (int j = 0; j < resultList.size(); j++) {
                List<Map<String,Object>> hostsList = businessService.getHostsBussinessInfo(resultList.get(j).get("business_id").toString());        //通过业务id获得主机信息
                List<Map<String,Object>> databasesList = businessService.getDatabasesBussinessInfo(resultList.get(j).get("business_id").toString());    //通过业务信息获得数据库信息
                List<Map<String,Object>> result = hostsList;
                for (int k = 0; k < databasesList.size(); k++) {
                    result.add(databasesList.get(k));
                }
                i = 0;
                sheet = wb.createSheet(resultList.get(j).get("name").toString());
                row = sheet.createRow(i);
                cell = row.createCell(0);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 2));
                i++;
                sheet.setColumnWidth(0,6000);
                sheet.setColumnWidth(1,6000);
                sheet.setColumnWidth(2,6000);
                // 单元格设值
                cell.setCellValue("时间： "+today);
                //设置单元格样式
                cell.setCellStyle(style);

                row = sheet.createRow(i);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 3));
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue(resultList.get(j).get("name")+"资源列表");
                //设置单元格样式
                cell.setCellStyle(style);

                row = sheet.createRow(i);
                i++;
                cell = row.createCell(0);
                // 单元格设值
                cell.setCellValue("资源名称");
                cell = row.createCell(1);
                // 单元格设值
                cell.setCellValue("IP");
                cell = row.createCell(2);
                // 单元格设值
                cell.setCellValue("类型");

                for (int k = 0; k < result.size(); k++) {
                    row = sheet.createRow(i);
                    i++;
                    cell = row.createCell(0);
                    // 单元格设值
                    cell.setCellValue(result.get(k).get("name").toString());
                    //设置单元格样式
                    cell.setCellStyle(style);

                    cell = row.createCell(1);
                    // 单元格设值
                    cell.setCellValue(result.get(k).get("ip").toString());
                    //设置单元格样式
                    cell.setCellStyle(style);

                    cell = row.createCell(2);
                    // 单元格设值
                    cell.setCellValue(result.get(k).get("type").toString());
                    //设置单元格样式
                    cell.setCellStyle(style);
                }


            }


            // 设定保存类型 excel
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            // 保存文件的名称
            String fileName = "业务统计报表";
            try {
                // 保存文件编码格式
                String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
                // 保存文件头部默认数据
                response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
                // 字节流保存
                OutputStream os = response.getOutputStream();
                // 写入
                wb.write(os);
                // 刷新缓冲区
                os.flush();
                // 关闭字节流
                os.close();
                return true;
            }catch (Exception e){
                e.getMessage();
                return false;
            }
        }
    }

    /**
     * 提供echart图数据
     * @param hostType
     * @return
     */
    @Override
    public List<Map<String, Object>> getRsource_charts(String hostType) {
        if ("0".equals(hostType)){  // 资源数据 分类型统计 服务器/交换机/单向网闸/防火墙
            List<Map<String,Object>> resultList = new ArrayList();

            int i = 0;
            int AvailSwitch=0,UnAvailSwitch=0;
            int AvailSingleGatekeeper=0,UnAvailSingleGatekeeper=0;
            int AvailFirewall = 0,UnAvailFirewall = 0;
            MonitorServiceImpl monitorService = new MonitorServiceImpl();
            PortServiceImpl portService = new PortServiceImpl();
            // 获得可用和不可用的数量
            final Integer count1 = monitorService.getHostCountByTypeAndStatus(0,"可用");
            final Integer count2 = monitorService.getHostCountByTypeAndStatus(0,"不可用");

            //服务器
            Map<String,Object> map1 = new HashMap<>();
            map1.put("type","服务器");
            map1.put("count_sum",count1+count2);
            map1.put("count_avail",count1);
            map1.put("count_unavail",count2);
            resultList.add(map1);


            final Integer count3 = monitorService.getHostCountByTypeAndStatus(1,"可用");
            List<Map<String,Object>> resultList3 = monitorService.getHostListAvailClassify(1,count3,"可用",1);

            final Integer count4 = monitorService.getHostCountByTypeAndStatus(1,"不可用");
            List<Map<String,Object>> resultList4 = monitorService.getHostListAvailClassify(1,count4,"不可用",1);
            for (int j = 0; j < resultList3.size(); j++) {
                Map<String,Object> map = (Map<String, Object>) resultList3.get(j).get("inventory");
                if ("firewall".equals(map.get("notes")))
                    AvailFirewall++;
                else if("single_gate".equals(map.get("notes")))
                    AvailSingleGatekeeper++;
                else
                    AvailSwitch++;
            }
            for (int j = 0; j < resultList4.size(); j++) {
                Map<String,Object> map = (Map<String, Object>) resultList4.get(j).get("inventory");
                if ("firewall".equals(map.get("notes")))
                    UnAvailFirewall++;
                else if("single_gate".equals(map.get("notes")))
                    UnAvailSingleGatekeeper++;
                else
                    UnAvailSwitch++;
            }

            //交换机
            Map<String,Object> map2 = new HashMap<>();
            map2.put("type","交换机");
            map2.put("count_sum",AvailSwitch+UnAvailSwitch);
            map2.put("count_avail",AvailSwitch);
            map2.put("count_unavail",UnAvailSwitch);
            resultList.add(map2);


            //单项网闸
            Map<String,Object> map3 = new HashMap<>();
            map3.put("type","单项网闸");
            map3.put("count_sum",AvailSingleGatekeeper+UnAvailSingleGatekeeper);
            map3.put("count_avail",AvailSingleGatekeeper);
            map3.put("count_unavail",UnAvailSingleGatekeeper);
            resultList.add(map3);


            //防火墙
            Map<String,Object> map4 = new HashMap<>();
            map4.put("type","防火墙");
            map4.put("count_sum",AvailFirewall+UnAvailFirewall);
            map4.put("count_avail",AvailFirewall);
            map4.put("count_unavail",UnAvailFirewall);
            resultList.add(map4);

            return resultList;
        }else   // 获得业务信息提供echart显示
            return businessService.getBusinessReport();
    }

    /**
     * 根据设备hostid获得一个可用设备
     * @param hostid
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllOnlineHost(String hostid) {
        final String token = APIUtils.getToken();
        ArrayList<String> imp_str = new ArrayList<>();
        imp_str.add("available");
        imp_str.add("snmp_available");
        imp_str.add("status");
        imp_str.add("hostid");
        imp_str.add("host");
        imp_str.add("name");
        Map<String, Object> paramsMap = new HashMap<String, Object>(){{
            put("jsonrpc", "2.0");
            put("method", "host.get");
            put("id", 1);
            put("auth", token);
            put("params", new HashMap<String, Object>(){{
                put("filter", new HashMap<String, Object>(){{
                    //只显示可用的设别
                    put("monitored_hosts", true);
                }});
                put("hostids",hostid);
                put("status",0);
                // 定义输出
                put("output",imp_str);
            }});
        }};
        // 结果序列化
        Map<String, Object> responseMap = APIUtils.sendRequest(JSON.toJSONString(paramsMap, SerializerFeature.WriteMapNullValue));
        List<Map<String, Object>> resultList = (List<Map<String, Object>>)responseMap.get("result");
        return resultList;
    }

    /**
     *根据设备hostid获得一个可用设备（无参）
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllOnlineHost() {
        DemoServiceImpl demo = new DemoServiceImpl();
        MonitorServiceImpl monitorService = new MonitorServiceImpl();
        List<Map<String,Object>> resultList = demo.getAllOnlineHost();
        // 筛选非网络设备主机
        for (int i = 0; i < resultList.size(); i++) {
            if ("1".equals(resultList.get(i).get("snmp_available")) || "1".equals(resultList.get(i).get("available")))
                continue;
            resultList.remove(i);
            i--;
        }
        String hostid;
        String item = "";
        for (int i = 0; i < resultList.size(); i++) {
            hostid = resultList.get(i).get("hostid").toString();
            JSONArray array = ((JSONArray) monitorService.getItemByHostidAndKey(hostid, "system.cpu.util").get("result"));//通过设备hostid何键值获得监控项
            if (!array.isEmpty())
                item = array.getJSONObject(0).getString("itemid");
            JSONArray array1 = (JSONArray) monitorService.getTrendByItemid(item).get("result");// 通过监控项获得趋势
            if (array1.isEmpty()){
                resultList.remove(i);
                i--;
            }
        }
        return resultList;
    }

    /**
     * 获得告警top10前端展示
     * @return
     */
    @Override
    public List<Map<String, Object>> getAlertTop10(String dateStr) {
        // 拿到API数据
        final String token = APIUtils.getToken();
        List<Map<String, Object>> problemDataList1 = problemService.getProblemsAndAcknowledgedProblems(token,"", "", dateStr,1);
//        List<Map<String, Object>> problemDataList = problemService.getProblems(token, true, "", "", dateStr);
        List<Map<String, Object>> problemDataList = problemService.getProblems_new( true, "", "", dateStr);
        for (int i=0;i<problemDataList1.size();i++)
            problemDataList.add(problemDataList1.get(i));
        Collections.sort(problemDataList, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Long name1 = Long.valueOf(o1.get("clock").toString()) ;
                Long name2 = Long.valueOf(o2.get("clock").toString()) ;
                return name2.compareTo(name1);
            }
        });
        if (problemDataList.size()>1000)
            problemDataList = problemDataList.subList(0,1000);
        //获得历史告警且排序
//        List<Map<String, Object>> problemDataList = getHistoryAlertAndOrder(startDate,endDate);
        List<Map<String, Object>> problemTop10 = new ArrayList<>();

        // 根据host统计个数
        for (int i = 0; i < problemDataList.size(); i++) {
            int j = 0;
            for (; j < problemTop10.size(); j++) {
                if (problemDataList.get(i).get("host").toString().equals(problemTop10.get(j).get("host").toString())){
                    problemTop10.get(j).put("count",(int) problemTop10.get(j).get("count")+1);
                    break;
                }
            }
            if (j==problemTop10.size()) {
                List<Map<String, Object>> finalProblemDataList = problemDataList;
                int finalI = i;
                problemTop10.add(new HashMap<String, Object>(){{
                    put("host", finalProblemDataList.get(finalI).get("host"));
                    put("count",1);
                }});
            }
        }

        // 排序
        Collections.sort(problemTop10, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                Integer name1 = Integer.valueOf(o1.get("count").toString()) ;
                Integer name2 = Integer.valueOf(o2.get("count").toString()) ;
                return name2.compareTo(name1);
            }
        });
        // 只取前十个
        if (problemTop10.size()>10) {
            problemTop10 = problemTop10.subList(0,10);
        }
        return problemTop10;
    }

    /**
     * 获得所有未处理告警信息
     * @param token
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllCurrentUnhandledProblems(String token,String startDate, String endDate) {
        String severities = "";
        String hostIds = "";
        final String[] outputs = new String[]{
                "object", "objectid", "clock", "name", "r_eventid", "severity"
        };

        final Map<String, String> searchMap = new HashMap<String, String>() {{
            // 筛选出尚未解决的问题
            put("r_eventid", "0");
        }};

        // 填入params的对应参数
        Map<String, Object> paramsMap = new HashMap<String, Object>() {{
            put("output", outputs);
            // 根据严重等级筛选
            put("severities", TextUtils.revertSeverity(severities));
            // 筛选未被确认的问题
            put("acknowledged", false);
            put("sortfield", "eventid");
            put("sortorder", "DESC");
            put("filter", new HashMap<String, Object>() {{
                // 开启筛选
                put("searchByAny", true);
                // 具体筛选，规则为LIKE "%...%"
                put("search", searchMap);
                // 从头开始搜索
                put("startSearch", true);
            }});
        }};

        // 筛选主机
        if (!(hostIds == null || hostIds.isEmpty())) {
            String[] temp = hostIds.split(",");
            paramsMap.put("hostids", temp);
        }

        // 填入最终参数
        Map<String, Object> parameterMap = new HashMap<String, Object>() {{
            put("jsonrpc", "2.0");
            put("method", "problem.get");
            put("id", 1);
            put("auth", token);
            put("params", paramsMap);
        }};

        Map<String, Object> responseMap = APIUtils.sendRequest(JSON.toJSONString(parameterMap, SerializerFeature.WriteMapNullValue));
        List<Map<String, Object>> resultList = (List<Map<String, Object>>) responseMap.get("result");

        // 继续查询host数据
        // 应该先分页再查询
        String[] eventidArray = new String[resultList.size()];
        for (int i = 0; i < resultList.size(); i++) {
            Map<String, Object> tempMap = resultList.get(i);
            eventidArray[i] = (String) tempMap.get("eventid");
        }

        final String[] selectHosts = new String[]{"host"};

        Map<String, Object> eventParamsMap = new HashMap<String, Object>() {{
            put("jsonrpc", "2.0");
            put("method", "event.get");
            put("id", 1);
            put("auth", token);
            put("params", new HashMap<String, Object>() {{
                put("eventids", eventidArray);
                put("selectHosts", selectHosts);
                put("time_from", DateUtils.timeToStamp(startDate)/1000);
                put("time_till", DateUtils.timeToStamp(endDate)/1000);
                // 与前面的problem.get排序规则相同
                put("sortfield", "eventid");
                // 同上
                put("sortorder", "DESC");
            }});
        }};
        // 结果序列化
        Map<String, Object> hostsMap = APIUtils.sendRequest(JSON.toJSONString(eventParamsMap, SerializerFeature.WriteMapNullValue));
        List<Map<String, Object>> hostsList = (List<Map<String, Object>>) hostsMap.get("result");

        // 将查到的host值放到之前的resultList内每条Map记录中
        for (int i = hostsList.size()-1; i >= 0; i--) {
            Map<String, Object> tempMap = hostsList.get(i);
            JSONArray tempArray = (JSONArray) tempMap.get("hosts");
            if (tempArray.isEmpty()) {
                // 该事件的主机已被删除
                hostsList.remove(i);
            } else {
                // 该事件的主机存在
                Map<String, Object> innerMap = tempArray.getJSONObject(0);
                hostsList.get(i).put("hostid", innerMap.get("hostid"));
                hostsList.get(i).put("host", innerMap.get("host"));
            }
        }

        return hostsList;
    }
}
